SUMIF

SUMIF(range, criteria [,sum_range])

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

rangeThe range of cells you want evaluated.
criteriaThe 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

 ABC
1=SUMIF(B1:B5, "Jan", C1:C5) = 50.0Jan30
2=SUMIF(B1:B5, "=Jan", C1:C5) = 50.0Feb40
3=SUMIF(B1:B5, "=Feb", C1:C5) = 100.0Jan20
4=SUMIF(C1:C5, "<>20") = 210.0Feb60
5=SUMIF(C1:C5, "=20", C1:C5) = 20.0Mar80
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 -

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