SUMIFS

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

Returns the total of the numerical values that satisfies multiple conditions.

sum_rangeThe range of cells to total.
criteria_range1The range of cells you want evaluated.
criteria1The expression that contains the criteria.
criteria_range2(Optional) The range of cells you want evaluated.
criteria2(Optional) The expression that contains the criteria.

REMARKS
* 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.
* You can use the IFS function to return the value based on multiple conditions.
* This is similar to the AVERAGEIFS, COUNTIFS, MAXIFS and MINIFS functions.
* This function was added in Excel 2007 to replace the SUMIF function.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 ABC
1=SUMIFS(C1:C5, B1:B5, "Jan") = 50Jan30
2=SUMIFS(C1:C5, B1:B5, "=Jan") = 50Feb40
3=SUMIFS(C1:C5, B1:B5, "=Feb") = 100Jan20
4=SUMIFS(C1:C5, B1:B5, "Jan", C1:C5, "<>20") = 30Feb60
5=SUMIFS(C1:C5, B1:B5, "Jan", C1:C5, "=20") = 20Mar80
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".

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top