# SUMIF

SUMIF(range, criteria [,sum_range])

Returns the total of the numerical values in a list, table or cell range 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
 * SUMIFS was added in Excel 2007 to replace this function.* 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.* This is similar to the AVERAGEIF and COUNTIF functions.* 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) = 50.0 Jan 30 2 =SUMIF(B1:B5, "=Jan", C1:C5) = 50.0 Feb 40 3 =SUMIF(B1:B5, "=Feb", C1:C5) = 100.0 Jan 20 4 =SUMIF(C1:C5, "<>20") = 210.0 Feb 60 5 =SUMIF(C1:C5, "=20", C1:C5) = 20.0 Mar 80 6 =SUMIF(C1:C5, ">" & C3, C1:C5) = 210.0 7 =SUMIF(B1:B5, D2) = 0 8 =SUMIF(B1:B4, "="&20/10) = 0 9 =SUMIF(B1:B5, ">12") = 0 10 =SUMIF(B3:B6, "="&20/10) = 0 11 =SUMIF(B3:B7, ">12") = 0 12 =SUMIF(B1:B4, ">16000") = 0

 1 - What is the sum of all the values in the range "C1:C5" which have 14,000 in the range "B1:B5".2 - This is the same as 1 except the "criteria" uses a cell reference.3 - This is the same as 1 except the "criteria" does not include an equal sign.4 - This is the same as 1 except the "criteria" is not contained in speech marks.5 - What is the sum of all the values in the range "C1:C5" which have more than 14,000 in the range "B1:B5". (600 + 800 + 1000)6 - This is the same as 5 except the "criteria" uses a cell reference.7 - If the "sum_range" is left blank, then the "range" of cells is summed.8 - What is the sum of all the values in the range "B1:B4" which have (7000+14000+21000+28000)/10 in the range "B1:B4".9 - What is the sum of all the values in the range "B1:B5" which are greater than the average of the values in the range "B1:B5".10 - What is the sum of all the values in the range "C1:C5" which have the letter "o" in the range "E1:E5".11 - What is the sum of all the values in the range "C1:C5" which have the letter "h" in the range "E1:E5".12 - 13 - 14 - 15 -