January 28, 2012 10 Comments
The SUMIF function returns the sum of a collection of numbers, including only numbers that satisfy a specified condition.
SUMIF(range, criteria, sum_range)
range: The collection containing the values to be tested. range-values is a collection containing any value type.
criteria: An expression that results in a logical TRUE or FALSE. criteria is an expression that can contain anything as long as the result from comparing criteria to a value in range can be expressed as a Boolean value of TRUE or FALSE.
sum-range: An optional collection containing the numbers to be summed. Sumrange is a collection containing number, date/time, or duration values. It should have the same dimensions as range.
Although range can contain any type of value, it should usually contain values all of the same type. If the sum_range is partially entered then the range will be taken as sum-range
Important 1: Criteria has to be in quotes unless you put criteria in a cell and refer to it with a cell reference.
Important 2: Criteria for greater than 10 could be :
2)”>”&A1 where 10 was in cell A1
3) A1 where >10 was in cell A1
In the sample data if we want to sum total value for the criteria “Pensil “
Formula will be
To make more generalized formula we can also write as
In the third argument (sum-range) if we ignore the whole range still function will works and the function will treat sum-range as range values
other way for achieving the same results for the above stated criteria
Using Sum Function
traditional way of writing with if function
Using Sumproduct Function
Using Filter function
Using Query function
=sum(query(A:F,”select F where C = ‘Pencil'”)
Using DSUM Function
where K2:P3 will as below
You are free to download the above file at the following link