=AVERAGEIF(range, criteria, [average_range])
Finds average (arithmetic mean) for the cells specified by a given condition or criteria
Region Sales
East 500
West 50
North 100
South 25
Mid West 200
South New Office 30
East 35
West 50
South 15
North 25
North New Office 40
5 50
5 100
2 200
4 35
2 45
4 50
1 90
0 100
1 125
1 115
0 55
Back to LIST OF FUNCTIONS
=SUMIF(range, criteria, [sum_range])
Adds the cells specified by a given condition or criteria
Region Sales
East 500
West 50
North 100
South 25
Mid West 200
South New Office 30
East 35
West 50
South 15
North 25
North New Office 40
5 50
5 100
2 200
4 35
2 45
4 50
1 90
0 100
1 125
1 115
0 55
Back to LIST OF FUNCTIONS
COUNTIF(range, criteria)
Counts the number of cells within a range that meet the given condition
Region Sales
East 500
West 50
North 100
South 25
Mid West 200
South New Office 30
East 35
West 50
South 15
North 25
North New Office 40
5 50
5 100
2 200
4 35
2 45
4 50
1 90
0 100
1 125
1 115
0 55
Back to LIST OF FUNCTIONS
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, cr
Finds average (arithmetic mean) for the cells specified by a given set of conditions or criteria
Product Sales Person Quantity Sold
Apple Tom 10
Orange Jon 4
Apple Marissa 12
Carrot Kawser 5
Banana Khan 13
Apple Tom 15
Banana Jon 14
Carrot Kawser 12
Orange Jon 8
Carrot Marissa 9
Apple Tom 15
Banana Jon 20
Carrot Marissa 25
Back to LIST OF FUNCTIONS
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], [c
Adds the cells specified by a given set of conditions or criteria
Product Sales Person Quantity Sold
Apple Tom 10
Orange Jon 4
Apple Marissa 12
Carrot Kawser 5
Banana Khan 13
Apple Tom 15
Banana Jon 14
Carrot Kawser 12
Orange Jon 8
Carrot Marissa 9
Apple Tom 15
Banana Jon 20
Carrot Marissa 25
Back to LIST OF FUNCTIONS
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_r
Counts the number of cells specified by a given set of conditions or criteria
Product Sales Person Quantity Sold
Apple Tom 10
Orange Jon 4
Apple Marissa 12
Carrot Kawser 5
Banana Khan 13
Apple Tom 15
Banana Jon 14
Carrot Kawser 12
Orange Jon 8
Carrot Marissa 9
Apple Tom 15
Banana Jon 20
Carrot Marissa 25
Back to LIST OF FUNCTIONS
IF(logical_test, [value_if_true], [value_if_false]
Checks whether a condition is met, and returns one value if TRUE, and another value is FALSE
Actual Expense Predicted Expense
1500 900
500 900
525 925
Back to LIST OF FUNCTIONS
IFERROR(value, value_if_error)
Returns value_if_error if expression is an error and the value of the expression itself otherwise
Quota Units Sold
210 35
55 0
25
Back to LIST OF FUNCTIONS
IFNA(value, value_if_na)
Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression
Post Box Code
Rampura 1219
Gulshan 1217
Dhamrai 1203
Motijheel 1200
Khilgaon 1000 If Value or Value_if_na is an empty cell, IFNA treats it as an empty s
If Value is an array formula, IFNA returns an array of results for each
iteria, [average_range])
specified by a given condition or criteria
Formula Result
=AVERAGEIF(B6:B27, "East", C6:C27) 267.5
=AVERAGEIF(B6:B27, "North", C6:C27) 62.5
=AVERAGEIF(B6:B27, "North*", C6:C27) 55
=AVERAGEIF(B6:B27, "*New Office", C6:C27) 35
=AVERAGEIF(B6:B27, ">=4", C6:C27) 58.75
=AVERAGEIF(B6:B27, 5, C6:C27) 75
=AVERAGEIF(B6:B27, TRUE(), C6:C27) 110
=AVERAGEIF(B6:B27, FALSE(), C6:C27) 77.5
Warnings
Cells in range that contain TRUE or FALSE are ignored.
If a cell in average_range is an empty cell, AVERAGEIF ignores it.
If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.
You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single cha
characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Average_range does not have to be the same size and shape as range. The actual cells that are averaged are determined b
beginning cell, and then including cells that correspond in size and shape to range.
[sum_range])
Formula Result
=SUMIF(B36:B57, "East", C36:C57) 535
=SUMIF(B36:B57, "North", C36:C57) 125
=SUMIF(B36:B57, "North*", C36:C57) 165
=SUMIF(B36:B57, "*New Office", C36:C57) 70
=SUMIF(B36:B57, ">=4", C36:C57) 235
=SUMIF(B36:B57, 5, C36:C57) 150
=SUMIF(B36:B57, TRUE(), C36:C57) 330
=SUMIF(B36:B57, FALSE(), C36:C57) 155
Warnings
The SUMIF function returns incorrect results when you use it to match strings longer than 255 characters or to the string #
The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are ad
in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range arg
The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are ad
in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range arg
at meet the given condition
Formula Result
=COUNTIF(B66:B87, "East") 2
=COUNTIF(B66:B87, "North") 2
=COUNTIF(C66:C87,">=100") 8
=COUNTIF(C66:C87, "<="&C68) 17
=COUNTIF(B66:B87, "No*") 3
=COUNTIF(B66:B87, "Ea??") 2
Warnings
The COUNTIF function returns incorrect results when you use it to match strings longer than 255 characters.
Be sure to enclose the criteria argument in quotes.
range, criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3
specified by a given set of conditions or criteria
Formula Result
=AVERAGEIFS(D96:D108, B96:B108, "Apple", C96:C108, "Tom") 13.3333333333333
=AVERAGEIFS(D96:D108, B96:B108, "Banana", C96:C108, "Marissa") #DIV/0!
=AVERAGEIFS(D96:D108, B96:B108, "Carrot", C96:C108, "Marissa") 17
Warnings
If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
Cells in range that contain TRUE evaluate as 1; cells in range that contain FALSE evaluate as 0 (zero). *Remember in AVERAG
neglected.
Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for th
Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same s
If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value.
If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.
You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single cha
characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
eria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3], …)
itions or criteria
Formula Result
=SUMIFS(D119:D131, B119:B131, "Apple", C119:C131, "Tom") 40
=SUMIFS(D119:D131, B119:B131, "Banana", C119:C131, "Marissa") 0
=SUMIFS(D119:D131, B119:B131, "Carrot", C119:C131, "Marissa") 34
ge1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3], …)
en set of conditions or criteria
Formula Result
=COUNTIFS(D142:D154, ">=10", D142:D154, "<=25") 9
=COUNTIFS(B142:B154, "Apple", C142:C154, "Tom") 3
=COUNTIFS(D142:D154, ">="&D142, C142:C154, "Marissa") 2
Warnings
Each range's criteria is applied one cell at a time. If all of the first cells meet their associated criteria, the count increases by
criteria, the count increases by 1 again, and so on until all of the cells are evaluated.
If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any sin
Warnings
Each range's criteria is applied one cell at a time. If all of the first cells meet their associated criteria, the count increases by
criteria, the count increases by 1 again, and so on until all of the cells are evaluated.
If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any sin
of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
_true], [value_if_false]
ns one value if TRUE, and another value is FALSE
Formula Result
=IF(B163>C163, "Over Budget", "OK") Over Budget
=IF(B164>C164,"Over Budget",IF(B165<C165,"Budget OK","Not OK")) Budget OK
f_error)
or and the value of the expression itself otherwise
Formula Result
=IFERROR(B173/C173, "Error in Calculations?") 6
=IFERROR(B174/C174, "Error in Calculations?") Error in Calculations?
=IFERROR(B175/C175, "Error in Calculations?") 0
n resolves to #N/A, otherwise returns the result of the expression
Formula Result
=IFNA(VLOOKUP("Marissa", B184:C188, 2,FALSE()), "Code is not Found") Code is not Found
Warnings
If Value or Value_if_na is an empty cell, IFNA treats it as an empty string value ("").
If Value is an array formula, IFNA returns an array of results for each cell in the range specified in value.
Remarks
Average of all the Sales for East region.
Average of all the Sales for North region.
Average of all the Sales for North region (including North (New Office) region).
Average of all the Sales for the New Offices.
Average of all the Sales for the values greater than or equal to 4.
Average of all the Sales for the values equal to 5.
Average of all the Sales for the values equal to TRUE statement.
Average of all the Sales for the values equal to FALSE statement.
rnings
question mark matches any single character; an asterisk matches any sequence of
e the character.
ls that are averaged are determined by using the top, left cell in average_range as the
Remarks
Sum of all the Sales for East region.
Sum of all the Sales for North region.
Sum of all the Sales for North region (including North (New Office) region).
Sum of all the Sales for the New Offices.
Sum of all the Sales for the values greater than or equal to 4.
Sum of all the Sales for the values equal to 5.
Sum of all the Sales for the values equal to TRUE statement.
Sum of all the Sales for the values equal to FALSE statement.
rnings
than 255 characters or to the string #VALUE!.
argument. The actual cells that are added are determined by using the upper leftmost cell
ond in size and shape to the range argument.
argument. The actual cells that are added are determined by using the upper leftmost cell
ond in size and shape to the range argument.
Remarks
Count the number of cells with East in cells B66 through B87.
Count the number of cells with North in cells B66 through B87.
Count the number of cells with values greater than and equal to 100.
Count the number of cells with values less than and equal to the value of cell C68.
Count the number of cells with values that start with "No" characters.
Count the number of cells with values that start with "Ea" characters and then have
any two characters.
rnings
er than 255 characters.
riteria_range3, criteria3], …)
Remarks
Average Quantity Sold of Apple product by Sales Person Tom.
Average Quantity Sold of Banana product by Sales Person Marissa. Marissa didn't sell
Banana. So #DIV/0! error is showing in the cell.
Average Quantity Sold of Carrot product by Sales Person Marissa.
rnings
ate as 0 (zero). *Remember in AVERAGEIF() function TRUE or FALSE statements were
onding criteria specified are true for that cell.
ach criteria_range must be the same size and shape as sum_range.
#DIV0! error value.
alue.
question mark matches any single character; an asterisk matches any sequence of
e the character.
ge3, criteria3], …)
Remarks
Sums the Quantity Sold of Apple product by Sales Person Tom.
Sums the Quantity Sold of Banana product by Sales Person Marissa. Marissa didn't sell
Banana. So #DIV/0! error is showing in the cell.
Sums the Quantity Sold of Carrot product by Sales Person Marissa.
eria3], …)
Remarks
Count the number of cells in the range D142: D154 that have values greater than or
equal to 10, and less than or equal to 25.
Count the number of rows from the ranges B142: B154 and C142: C154 that have
Apple and Tom values in them respectively.
Count the number of rows from the ranges D142: D154 and C142: C154 that have a
value greater than or equal to cell D142 and a value Marissa respectively.
rnings
ociated criteria, the count increases by 1. If all of the second cells meet their associated
the empty cell as a 0 value.
eria. A question mark matches any single character, and an asterisk matches any sequence
rnings
ociated criteria, the count increases by 1. If all of the second cells meet their associated
the empty cell as a 0 value.
eria. A question mark matches any single character, and an asterisk matches any sequence
fore the character.
Remarks
Simple IF formula.
Nested IF Formula. At first calculate the return value of the deepest IF function.
Deepest IF function means that IF function that does not have no more function
inside it.
Remarks
Returns 6 as the value argument does not return any error.
Returns the value_if_error argument as the value argument returns an error.
Though the cell B175 has nothing in it, but Excel treats nothing as Zero value, so the
formula returns 0.
Remarks
VLOOKUP function returns #N/A error when it does not find value. When #N/A is
returned, IFNA function returns the value of the value_if_na expression.
rnings
specified in value.
