SUM(IF()) array formula

Logical Excel formulas December 16th, 2007

This article basically compares excel’s vlookup and sumif array functions and explains which one to use, when and why?

Vlookup and Sumif functions can only work when there is just one criterion.
When we want to sort out data using multiple criteria then we can use SUM(IF()) array formula.

Like if you have the quantity sold of 5 products in year 2001 to 2005. And you want to know how much was it for each product in year 2001,2002 and so on.
So there are two criteria:
First, which product??
Second, Which Year??

We can handle the above situation using SUM(IF()) array formula.

The following screen would give you a better picture of what I am trying to explain:

Now if you enter
={SUM(IF(($A$6:$A$19=E5)*($B$6:$B$19=$F$4),$C$6:$C$19))} in cell F5
you will notice it is picking the right amount for each product and year.

Since it is an array formula, Press CTRL+SHIFT+ENTER instead of just ENTER.

Reader's Comments

Beau | April 9th, 2008 at 5:56 am
Hi,

I found this formula interesting, and in theory it looks like it should work for a value that i have been trying to figure out for some timenow (using all different kinds of methods), but with little success. Basically i am doing some statistics for KPI figures. The value that is giving me trouble is basically this:
I want to show the number of quotes won for a specific client in a specific month.

Using your formula above this is what i came up with:

={SUM(IF((Feb!C3:C18=ALL!A45)*(Feb!Feb!I3:I18=ALL!C44),countif(Feb!I3:I18,ALL!C44))}

Feb! = the name of the tab in which the first values reside.
C3:C18 = the name list of the client that each quote was performed for
All!A45 is the name of the specific client that i want to single out in the list of quotes.

Basically from here i have tried to get excel to count the number of times this client appears within C3:C18, but only if the corresponding value in column I3:I18 is equal to the word ‘won’ (All!C44 also is the word ‘won’.

Does this make sense? Hopefully you will have more luck than i have had.
himawan | April 10th, 2008 at 1:53 am
Dear Mr.Handy,
It was interested, but how to make array formula if I want to find average/max/min value from every product per years.
Thank u & I’ll waiting for your advise.
Rgds,
himawan
Handy Excel Tips | April 10th, 2008 at 7:49 pm
Hi Beau

Going through your explanation, it seems you want to count the number of entries in a particular column based on two criteria - Client’s Name and word “Won”. If that’s the case, then there is no need to enter countif function in your sumif array formula. You can simply use the following formula:
={SUM(IF((Feb!$A$3:$A$18=All!$A3)*(Feb!$B$3:$B$18=All!B$2),1,0))} assuming you have a list of clients in column A of feb tab and column B has won\lost entries.

You can also use a pivot table to get the count.
pat | February 22nd, 2009 at 4:09 pm
trying to use sumif comparing 2 variables in 2 separate columns. If both variables are true then the formula cell will include the value of the third column of the relative row.