Summing in Excel with Multiple Criteria

You use Excel when you have a lot of math to do on a bunch

of data. But,  things aren't

always that simple. For instance, you could easily use the SUM function to add

up all the numbers in a range of cells. But what do you do if you want to add

them up only if some condition is satisfied? Not to worry. For such cases, Excel

provides the SUMIF function and in this article we show you how to use it.

 For instance,

if you're a fruit merchant and you'd like to keep track of how many apples,

pears and oranges you have and how much they cost to your inventory, you'd

have an Excel worksheet like the ones in the figures below. Now, if you want to

add up the costs only for apples, you could use the SUMIF function without

needing to select only the rows with 'Apple' against them.

Similarly, you could add up the numbers for apples and oranges but not

pears and so on.

Yet another variant of the function is a case where we want

to perform a fuzzy addition such as summing for cases where the text can be

'Apples' or 'Apples and Oranges'.  Each

of these variants of the SUM function comes in handy when there is a lot of

data.

For a single criteria: The formula used in cell B10 is =SUMIF(A4:A8,A10, B4:B8). This sums if the fruits are Apples from Column A  The array formula in cell C10 is =SUM(IF((A4:A8= A10)+(B4:B8=B10), C4:C8)). Use for 'Apples' or the fruits are available ('Y' in column B) The array formula for C10 is =SUM(IF((A4:A8=A10)* (B4:B8=B10), C4:C8)). This sums if fruits are 'Apples' and they are available The array formula used in cell B8 is =SUM(IF(ISNUMBER(FIND(A8,$A$3:$A$6)),$B$3:$B$6)). If we change A8 to 'Apples', B8 would change to 300

AND/OR additions



Here, we show summing based on one criteria or multiple criteria, as

well as fuzzy additions. Screenshot 1 depicts addition based on a single

criteria. Screenshot 2 represents that based on 'multiple criteria (AND

condition)'. The difference between multiple criteria summing (OR condition,

Screenshot 3) and the previous multiple criteria formula (AND condition) is the

+ sign in this formula which implies 'OR'.

The * sign implies the 'AND' condition.

Fuzzy addition



These are additions where the criteria string may be contained within

another string in the summing range.  Let's sum for all occurrences of 'Oranges' appearing

independently or within another string.  Since

'Oranges' appear in row 4 as well as row 5, the amounts against both of them

should be summed. Therefore, we need to sum B3:B6 if there is 'Oranges' in

A3:A6.

Ashish Mathur