Multiple Criteria Sum

R

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
 
J

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
 
J

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
 
J

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
 
R

RD Wirr

That worked perfectly, Jacob. thanks very much

Jacob Skaria said:
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
 
R

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
 
R

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top