Excel Formula Notes (1–65)

1. SUM
Use: Adds numbers in a range.

Syntax: =SUM (number1, [number2], …)

Example: =SUM (A1:A5) → Adds all numbers from A1 to A5.

2. SUMIF
Use: Adds numbers based on one condition.

Syntax: =SUMIF (range, criteria, [sum range])

Example: =SUMIF (A1:A10, ">50", B1:B10)

3. SUMIFS
Use: Adds numbers based on multiple conditions.

Syntax: =SUMIFS (sum range, criteria_range1, criteria1, …)

Example: =SUMIFS (C1:C10, A1:A10, "Pen", B1:B10, "Red")

4. COUNT
Use: Counts numeric values in a range.

Syntax: =COUNT (value1, [value2], …)

Example: =COUNT (A1:A10)

5. COUNTA
Use: Counts all non-empty cells (numbers + text).

Syntax: =COUNTA (value1, [value2], …)

Example: =COUNTA (A1:A10)

6. COUNTBLANK
Use: Counts blank cells.

Syntax: =COUNTBLANK (range)

Example: Example: =COUNTBLANK (A1:A10)

7. COUNTIF
Use: Counts cells with one condition.
Syntax: =COUNTIF (range, criteria)

Example: =COUNTIF (A1:A10, ">50")

8. COUNTIFS
Use: Counts cells with multiple conditions.

Syntax: =COUNTIFS (criteria_range1, criteria1, …)

Example: =COUNTIFS (A1:A10, "Pen", B1:B10, "Blue")

9. IF
Use: Returns one value if condition is TRUE, another if FALSE.

Syntax: =IF (logical test, value_if_true, value_if_false)

Example: =IF(A1>=50, "Pass", "Fail")

10. IF(AND(...))
Use: Checks multiple conditions using AND (but all conditions must be TRUE).

Syntax: =IF (AND (condition1, condition2), value_if_true, value_if_false)

Example: =IF(AND(A1>=50, B1>=50), "Pass", "Fail")

11. IF(OR(...))
Use: Checks multiple conditions using OR. (Any one condition should be TRUE)

Syntax: =IF (OR (condition1, condition2), value_if_true, value_if_false)

Example: =IF(OR(A1>=50, B1>=50), "Pass", "Fail")

12. Nested IF
Use: Multiple IF statements together.

Syntax: =IF (condition1, value1, IF (condition2, value2, value3))

Example: =IF(A1>=80, "Excellent", IF(A1>=50, "Pass", "Fail"))

13. MAX
Use: Finds the largest value in a range.

Syntax: =MAX (number1, [number2], …)

Example: =MAX (A1:A10)

14. MIN
Use: Finds the smallest value in a range.
Syntax: =MIN (number1, [number2], …)

Example: =MIN (A1:A10)

15. AVERAGE
Use: Calculates the mean of numbers.

Syntax: =AVERAGE(number1, [number2], …)

Example: =AVERAGE(A1:A10)

16. AVERAGEIF
Use: Finds average with one condition.

Syntax: =AVERAGEIF(range, criteria, [average_range])

Example: =AVERAGEIF(A1:A10, ">50", B1:B10)

17. POWER
Use: Raises a number to a power.

Syntax: =POWER(number, power)

Example: =POWER(5,2) → 25

18. SQRT
Use: Returns square root.

Syntax: =SQRT(number)

Example: =SQRT(49) → 7

19. ABS
Use: Convert negative value into positive value

Syntax: =ABS(number)

Example: =ABS(-10) → 10

20. DOLLAR
Use: Converts number to text with currency format.

Syntax: =DOLLAR(number, [decimals])

Example: =DOLLAR(1234.567,2) → $1,234.57

21. WEEKDAY
Use: Returns day of week (1–7).
Syntax: =WEEKDAY (serial number, [return type])

Example: =WEEKDAY("01-Sep-2025") → 2

22. IF(WEEKDAY(...))
Use: Check day of week and return result.

Syntax: =IF(WEEKDAY(date)=1, "Sunday", "Not Sunday")

Example: =IF(WEEKDAY(A1)=7, "Saturday", "Other Day")

23. TEXT
Use: Formats numbers/dates as text.

Syntax: =TEXT(value, format_text)

Example: =TEXT(TODAY(),"DD-MMM-YYYY") → 01-Sep-2025

24. LOOKUP
Use: Finds value in row or column.

Syntax: =LOOKUP(lookup_value, lookup_vector, [result_vector])

Example: =LOOKUP(90, A1:A5, B1:B5)

25. VLOOKUP
Use: Searches vertically (first column).

Syntax: =VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

Example: =VLOOKUP(101, A2:D10, 3, FALSE)

26. HLOOKUP
Use: Searches horizontally (first row).

Syntax: =HLOOKUP(lookup_value, table_array, row_index, [range_lookup])

Example: =HLOOKUP(101, A1:H5, 3, FALSE)

27. TRANSPOSE
Use: Converts rows to columns or vice versa.

Syntax: =TRANSPOSE(array)

Example: =TRANSPOSE(A1:C3)

28. MATCH
Use: Returns the position of a value.
Syntax: =MATCH(lookup_value, lookup_array, [match_type])

Example: =MATCH(50, A1:A10, 0)

29. INDEX(MATCH(...))
Use: Flexible lookup using INDEX + MATCH.

Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_array,0))

Example: =INDEX(B1:B10, MATCH(50, A1:A10, 0))

30. LEFT
Use: Extracts characters from left.

Syntax: =LEFT(text, num_chars)

Example: =LEFT("Excel",2) → "Ex"

31. RIGHT
Use: Extracts characters from right.

Syntax: =RIGHT(text, num_chars)

Example: =RIGHT("Excel",2) → "el"

32. MID
Use: Extracts text from middle.

Syntax: =MID(text, start_num, num_chars)

Example: =MID("Excel",2,3) → "xce"

33. EXACT
Use: Compares two texts (case sensitive).

Syntax: =EXACT(text1,text2)

Example: =EXACT("Excel","excel") → FALSE

34. EVEN
Use: Rounds number up to nearest even.

Syntax: =EVEN(number)

Example: =EVEN(5) → 6

35. ODD
Use: Rounds number up to nearest odd.
Syntax: =ODD(number)

Example: =ODD(6) → 7

36. LOWER
Use: Converts text to lowercase.

Syntax: =LOWER(text)

Example: =LOWER("EXCEL") → "excel"

37. UPPER
Use: Converts text to uppercase.

Syntax: =UPPER(text)

Example: =UPPER("excel") → "EXCEL"

38. PROPER
Use: Capitalizes first letter of each word.

Syntax: =PROPER(text)

Example: =PROPER("excel formulas") → "Excel Formulas"

39. LEN
Use: Returns number of characters.

Syntax: =LEN(text)

Example: =LEN("Excel") → 5

40. CONCATENATE
Use: Joins text strings.

Syntax: =CONCATENATE(text1,text2, …)

Example: =CONCATENATE("Excel ","Formulas")

41. TRIM
Use: Removes extra spaces.

Syntax: =TRIM(text)

Example: =TRIM(" Excel Formulas ") → "Excel Formulas"

42. TODAY
Use: Returns current date.
Syntax: =TODAY()

Example: 01-Sep-2025

43. NOW
Use: Returns current date and time.

Syntax: =NOW()

Example: 01-Sep-2025 7:45 PM

44. DATE
Use: Creates a date.

Syntax: =DATE(year,month,day)

Example: =DATE(2025,9,1)

45. DAY
Use: Returns day from date.

Syntax: =DAY(serial_number)

Example: =DAY("01-Sep-2025") → 1

46. YEAR
Use: Returns year from date.

Syntax: =YEAR(serial_number)

Example: =YEAR("01-Sep-2025") → 2025

47. MONTH
Use: Returns month from date.

Syntax: =MONTH(serial_number)

Example: =MONTH("01-Sep-2025") → 9

48. DATEDIF
Use: Difference between two dates.

Syntax: =DATEDIF(start_date,end_date,unit)

Example: =DATEDIF("01-Jan-2025","01-Sep-2025","m") → 8

49. IFERROR
Use: Handles errors.
Syntax: =IFERROR(value,value_if_error)

Example: =IFERROR(1/0,"Error")

50. IFERROR(VLOOKUP(...))
Use: Handles VLOOKUP errors.

Syntax: =IFERROR(VLOOKUP(...),"Not Found")

Example: =IFERROR(VLOOKUP(101,A2:B10,2,FALSE),"Not Found")

51. NOT
Use: Reverses logical value.

Syntax: =NOT(logical)

Example: =NOT(TRUE) → FALSE

52. CHAR
Use: Returns character from number code.

Syntax: =CHAR(number)

Example: =CHAR(65) → "A"

53. CLEAN
Use: Removes non-printable characters.

Syntax: =CLEAN(text)

Example: =CLEAN(A1)

54. REPLACE
Use: Replaces part of a text.

Syntax: =REPLACE(old_text,start_num,num_chars,new_text)

Example: =REPLACE("Excel",2,3,"ABC") → "EABC"

55. SEARCH
Use: Finds position of text (not case-sensitive).

Syntax: =SEARCH(find_text,within_text,[start_num])

Example: =SEARCH("c","Excel") → 3

56. REPT
Use: Repeats text.
Syntax: =REPT(text,number_times)

Example: =REPT("A",5) → "AAAAA"

57. PMT
Use: Calculates loan payment per period.

Syntax: =PMT(rate,nper,pv,[fv],[type])

Example: =PMT(10%/12,12*5,-100000)

58. NPV
Use: Net Present Value.

Syntax: =NPV(rate,value1,[value2], …)

Example: =NPV(10%,A1:A5)

59. HYPERLINK
Use: Creates a clickable link.

Syntax: =HYPERLINK(link_location,[friendly_name])

Example: =HYPERLINK("[Link]

60. PRODUCT
Use: Multiplies all numbers.

Syntax: =PRODUCT(number1,[number2], …)

Example: =PRODUCT(2,3,4) → 24

61. FACT
Use: Factorial of number.

Syntax: =FACT(number)

Example: =FACT(5) → 120

62. MAXA
Use: Finds the largest value in a range, including numbers, logical values (TRUE/FALSE),
and text.

Where: TRUE = 1

FALSE = 0

Text = 0
Syntax: =MAXA(value1, [value2], …)

Example: =MAXA(10,"20",TRUE) → 20

63. MINA
Use: Finds the smallest value in a range, including numbers, logical values (TRUE/FALSE),
and text.

Where: TRUE = 1

FALSE = 0

Text = 0

Syntax: =MINA(value1, [value2], …)

Example: =MINA(10,"5",FALSE) → 0

64. SUBTOTAL
Use: Used to calculate summary values (SUM, AVERAGE, COUNT, MAX, MIN, etc.) for a
range, and It is special because it can ignore hidden or filtered rows.

function_num → A number (1–11 or 101–111) that tells Excel what calculation to do.

range1, range2… → The cell ranges where you want to apply the calculation.

Syntax: =SUBTOTAL(function_num, ref1, [ref2])

Example: =SUBTOTAL(9,A1:A10)

65. MOD
Use: Remainder after division.

Syntax: =MOD(number,divisor)

Example: =MOD(10,3) → 1