Multiple Criteria Sum

  • Thread starter Thread starter RD Wirr
  • Start date Start date
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
 
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
 
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
 
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
 
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
Click to expand...
 
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
 
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
 
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
 
Back
Top