MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sumif with two ranges and two criteria


Posted by George on March 22, 2001 11:36 AM

Col A has dates Jan-01....Dec-01 (range)
Col B has types of mail (i.e. 1st, 2nd...4th)(range)
Col C has mailing volumes (sum_range)

Would like to sum Col C
If Col A is Jan-01 and Col B is 1st (criteria)

Maybe a sumif(and statement if possible


Posted by Aladin Akyurek on March 22, 2001 12:06 PM

Enter your critera in separate cells, say in D1 for date and D2 for types of mail.

Array-enter (that is, hit CONTROL+SHIFT+ENTER at the same time to enter) the following:

C1 =SUM((A1:A5=D1)*(B1:B5=D2)*C1:C5)

or just array-enter:

C1 =SUM((A1:A5=VALUE("01-jan-01"))*(B1:B5="1st")*C1:C5)

Aladin

Posted by Aladin Akyurek on March 22, 2001 12:17 PM

: Col B has types of mail (i.e. 1st, 2nd...4th)(range) : Col C has mailing volumes (sum_range) : If Col A is Jan-01 and Col B is 1st (criteria)

The array-formula should NOT be entered in C1, but in some other cell, say in E1.

Posted by Ian on March 22, 2001 12:29 PM

First Formula

Aladin,
Could you dummy down the first formula for me and tell me what it is doing?
Thanks,
Ian

Posted by Aladin Akyurek on March 22, 2001 12:39 PM

Re: First Formula

Ian

You mean what will happen when you copy down, say to e2:e5? I don't think that is needed in George's case. If you do that, the ranges will change of course. Is that what you're asking?

Posted by Ian on March 22, 2001 12:50 PM

Re: First Formula

No, I'm sure it works fine, I mean I just don't understand the logic. I've seen several array formula like this with multipliers and I was wondering if you might be able to walk me through how/why it works. Maybe it is more of a MR.Math question! Also, I owe you and many of the other usual posting suspects belated thanks for all I've learned through this venue.

Thanks again,
Ian

Posted by Aladin Akyurek on March 22, 2001 1:10 PM

Re: First Formula

I've learned now something valuable too: what "dummying down" means!

But, back to business. I think the following would be very instructive.

Go to the formula bar, select a term, and hit F9. You can keep doing this until you see how each term gets evaluated.

You get to see the following:

=SUM(({FALSE;FALSE;TRUE;FALSE;FALSE})*({TRUE;FALSE;TRUE;TRUE;TRUE})*{2;1;3;4;5})

Don't leave the formula bar; select the first two terms, and hit F9.

You get to see the following:

=SUM({0;0;1;0;0}*{2;1;3;4;5})

[Note that FALSE=0 and TRUE=1, meaning that you can multiply logical values.]

Iterate. These two arrays gets multiplied (all this is a simple bit of linear algebra).

You get now the following:

=SUM({0;0;3;0;0})

Summing over the array {0;0;3;0;0} results in 3.

I use F9 when I need debugging any formula.

Aladin

Posted by George on March 22, 2001 1:40 PM

Re:thanks Aladin

: Col B has types of mail (i.e. 1st, 2nd...4th)(range) : Col C has mailing volumes (sum_range) : If Col A is Jan-01 and Col B is 1st (criteria)

Posted by Ian on March 22, 2001 1:43 PM

I got it now, Thanks again

I didn't know that True and False carried a 1 and 0 value that could be used like that.