Multiple Criteria Sum
 

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

Multiple Criteria Sum
 

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
---------------
Jacob Skaria


"RD Wirr" wrote:

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

Multiple Criteria Sum
 

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

"RD Wirr" wrote in message
...
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

Multiple Criteria Sum
 

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"RD Wirr" wrote:

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

Multiple Criteria Sum
 

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 he
http://sulprobil.com/html/sfreq.html

Regards,
Bernd

Multiple Criteria Sum
 

That worked perfectly, Jacob. thanks very much

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"RD Wirr" wrote:

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

Multiple Criteria Sum
 

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

"JP Ronse" wrote:

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

"RD Wirr" wrote in message
...
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

Multiple Criteria Sum
 

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" wrote:

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 he
http://sulprobil.com/html/sfreq.html

Regards,
Bernd

Multiple Criteria Sum
 

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