Multiple Criteria Sum

[RD Wirr]

I have data like the table below. I need to sum the numbers in column C based
on criteria in columns A and B. Something like a multiple criteria SUMIF


A B C
1 a red 1
2 a red 2
3 a white 2
4 b blue 2
5 c red 2

I need to have a formula that allows the criteria to be changed for each
cell in a sheet like below that sums the values in the array above based on
the values in Row 1 and Column A.

A B C
1 Red Blue
2 a A2,B1 A2,B2
3 b A3,B1 A3,B2
4 c A4,B1 A4,B2

Thanks in advance
RDW

 

[Jacob Skaria]

Use SUMPRODUCT() as below

=SUMPRODUCT(--(A2:A6="a"),--(B2:B6="red"),C2:C6)

You can change the string variables "a" and "red" to a cell reference to
suit your requirement.

If this post helps click Yes

 

[JP Ronse]

Hi,

Try ...

=SUM(($A$1:$A$5=$A10)*($B$1:$B$5=B$9)*($C$1:$C$5)) in A10:C12

A B C
9 Red Blue
10 a
11 b
12 c

Wkr,

JP

 

[Jacob Skaria]

Copy the below formula to B2 and copy down and across.
=SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$100=B$1),$C$2:$C$100)


If this post helps click Yes

 

[Bernd P]

Hello,

I suggest to select a range of 4 rows and 3 columns and to array-
enter:
=Sfreq(A1:A5,B1:B5,C1:C5)

Sfreq is a UDF which you can find here:
http://sulprobil.com/html/sfreq.html

Regards,
Bernd

 

[RD Wirr]

That worked perfectly, Jacob. thanks very much

Copy the below formula to B2 and copy down and across.
=SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$100=B$1),$C$2:$C$100)


If this post helps click Yes

 

[RD Wirr]

Hi JP,

Thanks, it took me a few minutes to figure out I had to enter this as an
array but now I got it. Works good thanks.
RD

 

[RD Wirr]

Hi Bernd,

I tried your UDF and it works well. Thanks for that. But is there a way to
make the UDF follow along with the file I am working on? This spreadsheet is
for other people to use and it will be too complicated to get them to run
this bit of code each time they open the spreadsheet. I guess you can tell I
am no programmer...

Thanks,
RD

 

[Bernd P]

Hello RD,

Can't you just store the UDF with your spreadsheet?

You can use a long range down to 999 within the formula. It would
update as a normal worksheet function.

Regards,
Bernd