SUMIFS

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

Returns the total value of the numbers 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 prefix them with a tilde (~?) 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 value of the numbers.
* 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.
* 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,"Barry") = 50	Barry	30
2	=SUMIFS(C1:C5,B1:B5,"=Barry") = 50	Simon	40
3	=SUMIFS(C1:C5,B1:B5,"=Simon") = 40	Barry	20
4	=SUMIFS(C1:C5,B1:B5,"Barry",C1:C5,"<>20") = 30	James	60
5	=SUMIFS(C1:C5,B1:B5,"Barry",C1:C5,"=20") = 20	David	80
6	=SUMIFS(C1:C5,B1:B5,"Barry",C1:C5,">"&C3) = 30

1 - What is the sum of all the numbers in the range "C1:C5" that have "Barry" in the range "B1:B5".
2 - What is the sum of all the numbers in the range "C1:C5" that have "Barry" in the range "B1:B5". Notice that prefixing the condition with an equal sign means the same thing.
3 - What is the sum of all the numbers in the range "C1:C5" that have "Simon" in the range "B1:B5".
4 - What is the sum of all the numbers in the range "C1:C5" that have "Barry" 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 "Barry" 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 "Barry" in the range "B1:B5" and values are not greater than the number in cell "C3".