Countif and Sumif are limited to testing against just one condition, unless

you use array formulas, which are resource hogs. DSUM is good but requires

you to setup a separate table somewhere. Sumproduct is a way to run a count

or sum meeting multiple criteria without a resource-hungry array and without

manually setting up a secret table somewhere. It's an invaluable asset to

Excel.

When you want it to sum, you indicate the range to sum at the end of the

formula...when you want it to count, you don't add the sum range at the end.

So if I didn't want the Count of instances of "2" but instead wanted the

Sum, I would've added the range to be summed at the end (which would give me

an answer of 8)

=SUMPRODUCT((Mapped!M2:M12="USC")*(Mapped!B2:B12="2008-10")*(Mapped!AC2:AG12=2)*(Mapped!AC2:AG12))

The formula essentially creates virtual tables to run each test. So for the

first test it goes down M2:M12 and creates a TRUE or FALSE answer for each.

Right next to that table it sets up another virtual column of answers for the

B2:B12 test with more T/F answers, then AC2:AC12 is the next column in the

virtual table with virtual T/F answers, etc. Once all the tests are run and

the virtual table is complete, it goes across the first row of that virtual

table and multiplies the answers...T * T = 1, but multiplying by an F results

in 0.

So your virtual table created by the sumproduct formula is:

Row 2:

TRUE (M2) *TRUE (B2) *FALSE (AC2) = 0 (for the sum version, it takes the 1

or 0 and multiplies by the value you wanted to pull)

T*T*T (AD2) = 1 (so the sum version would be 1*2=2

T*T*F (AE2) = 0

and so on, then it goes to Row 3:

T (M3) *T (B3) *F (AC3) = 0

and so on until the table is done. Then you get either the total count (4)

or the total sum (8).

The rules you have to follow are pretty easy:

1) The height of the range must be consistent for each test. So the second

test (B2:B12) could have been B13:B23 if that's the appropriate testing range

because it remains 11 rows tall. B13 would be used alongside the M2 and AC2

tests, B14 would correspond to M3 and AC3, etc. This works well when you

have multiple sheets and stuff is not necessarily on the same rows from sheet

to sheet.

2) TRUE is treated as the number 1 by Excel, and FALSE as 0. However, some

people by habit put a double-negative (--) in front of all tests just to be

sure that the T & F results are indeed treated as 1s and 0s. So it would

have looked like this

=SUMPRODUCT(--(Mapped!M2:M12="USC")*--(Mapped!B2:B12="2008-10")*--(Mapped!AC2:AG12=2))

3) You can use either the asterisk or a comma in the sumproduct formula. So

it could have been set up like this as well

=SUMPRODUCT((Mapped!M2:M12="USC"),(Mapped!B2:B12="2008-10"),(Mapped!AC2:AG12=2))

Hope that helps!