| | | Remember that it is the actual cell values which are used and not the values that are displayed. |
| | | 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. |
| | | This function is basically a combination of the SUM() and IF() functions. |
| | | Any #N/As in the range criteria are ignored. |
| | | Example 1 - What is the sum of all the values in the range "C1:C5" which have 14,000 in the range "B1:B5". |
| | | Example 2 - This is the same as Example 1 except the "criteria" uses a cell reference. |
| | | Example 3 - This is the same as Example 1 except the "criteria" does not include an equal sign. |
| | | Example 4 - This is the same as Example 1 except the "criteria" is not contained in speech marks. |
| | | Example 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) |
| | | Example 6 - This is the same as Example 5 except the "criteria" uses a cell reference. |
| | | Example 7 - If the "sum_range" is left blank, then the "range" of cells is summed. |
| | | Example 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". |
| | | Example 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". |
| | | Example 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". |
| | | Example 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". |
| | | Example 12 - Any ideas ? |
| | | For examples of how to include multiple conditions, please refer to Array Formulas > Multiple Conditions |
| | | | A | B | C | D | E | | 1 | =SUMIF(B1:B5,"=14000",C1:C5) = 400 | 7000 | 200 | 14000 | one | | 2 | =SUMIF(B1:B5,D1,C1:C5) = 400 | 14000 | 400 | >14000 | two | | 3 | =SUMIF(B1:B5,"14000",C1:C5) = 400 | 21000 | 600 | | three | | 4 | =SUMIF(B1:B5,14000,C1:C5) = 400 | 28000 | 800 | | four | | 5 | =SUMIF(B1:B5,">14000",C1:C5) = 2400 | 36000 | 1000 | | 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 | | | | |
| |
