SUMIF(range, criteria [,sum_range]) 
Returns the total value of the numbers that satisfies one condition. 
range  The range of cells you want evaluated. 
criteria  The 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 rows. * This function is not case sensitive when matching text strings. * This function supports wildcards (? and *). * Wildcard character: ? = a single character. * Wildcard character: * = multiple characters. * 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 SUMPRODUCT function to return the sum of the product of one or more arrays of values. * You can use the SUMIFS function to return the total value of the numbers that satisfies multiple conditions. * You can use the SUMIFSVISIBLE  User Defined Function if you want to exclude hidden rows. * This is similar to the AVERAGEIF function. * This is similar to the COUNTIF function. * For an illustrated example refer to the page under Advanced Functions. * For the Microsoft documentation refer to support.office.com 

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". 
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited Top