SUMIF(range, criteria [,sum_range])

Returns the total value of the numbers 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

* This function includes hidden rows.
* This function uses the actual cell values and not the displayed formatted values.
* 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.
* You can use the SUM function to return the total value of the numbers.
* You can use the SUMIFS function to satisfy multiple conditions.
* You can use the AVERAGEIF function to return the arithmetic mean of all the numbers that satisfies one condition.
* You can use the COUNTIF function to return the number of non blank cells that satisfies one condition.
* For an illustrated example refer to the page under Advanced Functions.
* For the Microsoft documentation refer to support.office.com

 ABCDE
1=SUMIF(B1:B5,"=14000",C1:C5) = 400700020014000one
2=SUMIF(B1:B5,D1,C1:C5) = 40014000400>14000two
3=SUMIF(B1:B5,"14000",C1:C5) = 40021000600 three
4=SUMIF(B1:B5,14000,C1:C5) = 40028000800 four
5=SUMIF(B1:B5,">14000",C1:C5) = 2400360001000 five
6=SUMIF(B1:B5,D2,C1:C5) = 2400    
7=SUMIF(B1:B5,D2) = 85000    
8=SUMIF(B1:B4,"="&SUM(B1:B4)/10) = 7000    
9=SUMIF(B1:B5,">"&AVERAGE(B1:B5)) = 64000    
10=SUM(IF(ISERROR(SEARCH("o",E1:E5))=FALSE,C1:C5,0)) = 1400    
11=SUM(IF(ISERROR(SEARCH("h",E1:E5))=FALSE,C1:C5,0)) = 600    
12=SUMIF(B1:B4,">16000",C1:C2) = 1400    

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 - Array Formula. What is the sum of all the values in the range "C1:C5" which have the letter "o" in the range "E1:E5".
11 - Array Formula. What is the sum of all the values in the range "C1:C5" which have the letter "h" in the range "E1:E5".

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