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