Sumif with two ranges and two criteria

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

: 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.

First Formula

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

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?

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

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

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)

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.