SUMIF

SUMIF(range, criteria [,sum_range])

Returns the total of the numerical values that satisfies one condition.

range	The range of cells you want evaluated.
criteria	The expression that contains the criteria.
sum_range	(Optional) The actual cells to sum.

REMARKS

* This function is only available for backwards compatibility and should not be used.
* SUMIFS was added in Excel 2007 to replace this function.
* When replacing this function with the SUMIFS function, remembering to change the order of the arguments.
* This function includes hidden cells.
* This function is not case sensitive when matching text strings.
* This function supports wildcards (? and *).
* Wildcard character: ? = a single character.
* Wildcard character: * = multiple characters.
* To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~).
* This function uses the actual cell values and not the displayed formatted values.
* This function automatically finds the last used row in a column reference.
* The "range" must be a cell range or a named range.
* The "range" does not have to be sorted into any order.
* The "criteria" can be expressed as 32, "32", ">32", "apples".
* The "criteria" can be made up of formulas and functions, see Example 8.
* If "sum_range" is left blank, then the cells in "range" are summed.
* The cells in "sum_range" are summed only if their corresponding cells in "range" match the criteria.
* Any #N/As in the range criteria are ignored.
* You can use the IF function to return a value based on one condition.
* You can use the SUM function to return the total of the numerical values.
* You can use the SUMIFS function to return the total of the numerical values that satisfies multiple conditions.
* This is similar to the AVERAGEIF and COUNTIF functions.
* This function has been available since Excel 1997.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C
1	=SUMIF(B1:B5, "Jan", C1:C5) = 60	Jan	20
2	=SUMIF(B1:B5, "=Jan", C1:C5) = 60	Feb	30
3	=SUMIF(C1:C5, "<0") = 0	Jan	40
4	=SUMIF(C1:C5, ">40") = 140	Feb	60
5	=SUMIF(C1:C5, "<>0") = 230	Mar	80
6	=SUMIF(C1:C5, "=20") = 20
7	=SUMIF(C1:C5, ">" & C3) = 140	01 Jan 2025
8	=SUM(C1:C6) = 230	02 Jan 2025
9	=SUMIF(B1:B6, "*", C1:C6) = 230	03 Jan 2025
10	=SUMIF(B1:B6, "B", C1:C6) = 90	04 Jan 2025
11	=SUMIF(B1:B6, "b", C1:C6) = 90	05 Jan 2025
12	=SUMIF(B1:B6, "???", C1:C6) = 230
13	=SUMIF(B1:B6, "?e*", C1:C6) = 90
14	=SUMIF(B7:B11, ">02/01/2025", C1:C5) = 180
15	=SUMIF(B7:B11, ">02 Jan 2025", C1:C5) = 180
16	=SUMIF(B7:B11, ">"&DATE(2025, 1, 2), C1:C5) = 180
17	=SUMIF(B7:B11, "<"&TODAY(), C1:C5) = 0
18	=SUMIF(B1:B5, "JAN", C1:C5)+SUMIF(B1:B5, "feb", C1:C5) = 150
19	=SUMIF(C1:C5, ">20")-SUMIF(C1:C5, ">=80") = 130
20	=SUM((C1:C5>20) * (C1:C5<80) * (C1:C5)) = 130

1 - What is the sum of the values in the range "C1:C5" that contain the value "Jan" in the range "B1:B5".
2 - What is the sum of the values in the range "C1:C5" that contain the value "Jan" in the range "B1:B5".
3 - What is the sum of the values in the range "C1:C5" that contain a negative number.
4 - What is the sum of the values in the range "C1:C5" that contain a number greater than 40.
5 - What is the sum of the values in the range "C1:C5" that contain a non zero value.
6 - What is the sum of the values in the range "C1:C5" that contain the number 20.
7 - What is the sum of the values in the range "C1:C5" that contains a number greater than 40.
8 - What is the sum of the values in the range "C1:C6" that contain numerical values.
9 - What is the sum of the values in the range "C1:C6" that contain text in the range "B1:B6".
10 - What is the sum of the values in the range "C1:C6" that contain the letter "B" in the range "B1:B6". This is not case sensitive.
11 - What is the sum of the values in the range "C1:C6" that contain the letter "b" in the range "B1:B6". This is not case sensitive.
12 - What is the sum of the values in the range "C1:C6" that contain only three letters in the range "B1:B6".
13 - What is the sum of the values in the range "C1:C6" that have the letter "e" as their second character in the range "B1:B6".
14 - What is the sum of the values in the range "C1:C5" that contain a date greater than "02/01/2025" in the range "B7:B11".
15 - What is the sum of the values in the range "C1:C5" that contain a date greater than "2 Jan 2025" in the range "B7:B11".
16 - What is the sum of the values in the range "C1:C5" that contain a date greater than "2 Jan 2025" in the range "B7:B11".
17 - What is the sum of the values in the range "C1:C5" that contain a date greater than today in the range "B7:B11".
18 - What is the sum of the values in the range "C1:C5" that contain the value "JAN" or "feb" in the range "B1:B5". This is not case sensitive.
19 - What is the sum of the values in the range "C1:C5" that contain a value greater than 20 and less than 80 in the range "B1:B5".
20 - What is the sum of the values in the range "C1:C5" that contain a value greater than 20 and less than 80 in the range "B1:B5".