SUMIFS

SUMIFS(sum_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..])

Returns the total of the numerical values in a list, table or cell range that satisfies multiple conditions.

 sum_range The range of cells to total. criteria_range1 The range of cells you want evaluated. criteria1 The expression that contains the criteria. criteria_range2 (Optional) The range of cells you want evaluated. criteria2 (Optional) The expression that contains the criteria.

 REMARKS
 * This function was added in Excel 2007 to replace the SUMIF function.* For an illustrated example refer to the page under Advanced Functions* 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.* The "criteria" can contain wildcard characters.* You can have a maximum of 127 (criteria_ranges and criteria).* You can use the SUM function to return the total of the numerical values.* You can use the SUMPRODUCT function to return the sum of the product of one or more arrays of values.* You can use the SUMVISIBLEIFS - User Defined Function to exclude hidden cells.* You can use the DSUM function to return the total of the values in a column that satisfies multiple conditions.* This is similar to the AVERAGEIFS, COUNTIFS, MAXIFS and MINIFS functions.* For the Microsoft documentation refer to support.microsoft.com* For the Google documentation refer to support.google.com

 A B C 1 =SUMIFS(C1:C5, B1:B5, "Jan") = 50 Jan 30 2 =SUMIFS(C1:C5, B1:B5, "=Jan") = 50 Feb 40 3 =SUMIFS(C1:C5, B1:B5, "=Feb") = 100 Jan 20 4 =SUMIFS(C1:C5, B1:B5, "Jan", C1:C5, "<>20") = 30 Feb 60 5 =SUMIFS(C1:C5, B1:B5, "Jan", C1:C5, "=20") = 20 Mar 80 6 =SUMIFS(C1:C5, B1:B5, "Jan", C1:C5, ">" & C3) = 30

 1 - What is the sum of all the numbers in the range "C1:C5" that have "Jan" in the range "B1:B5".2 - What is the sum of all the numbers in the range "C1:C5" that have "Jan" in the range "B1:B5".3 - What is the sum of all the numbers in the range "C1:C5" that have "Feb" in the range "B1:B5".4 - What is the sum of all the numbers in the range "C1:C5" that have "Jan" in the range "B1:B5" and values are not equal to 20.5 - What is the sum of all the numbers in the range "C1:C5" that have "Jan" in the range "B1:B5" and values are equal to 20.6 - What is the sum of all the numbers in the range "C1:C5" that have "Jan" in the range "B1:B5" and values are not greater than the number in cell "C3".