Expanded sumproduct

S

Steven

I have this and it works fine:

=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30002))

But how do I include the the column B and C columns and criteria into the
2nd formula.

Thank you,

Steven
 
D

Don Guillett

I may not be understanding what you want but try
=SUMPRODUCT(A14:A30002={"A","B","C"}),D14:D30002)
 
S

Steven

What I am looking for is that the 1st function has sumproduct on three
different columns but the criteria is only one item. The 2nd function has
one column but multiple criteria How do I make the formula so I can sum on
multiple criteria in column A ; and then also include in the function columns
B and C and their criteria.

Thank you,

Steven
 
S

Steven

Basically:

Why does this not work:

=SUMPRODUCT((SUMIF($A$14:$A$30002,{"A","B"}))*($B$14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002))

where column D are the amounts.

Thank you,


Steven
 
R

Ragdyer

=SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14:B$30002=$A$3)
*($C$14:$C$30002=$C$2)*$D$14:$D$30002)
 
S

Steven

One last question:

Instead of {"A","B","C"} is there a way to use cell references? I have
tried many things without sucess.

Thank you,

Steven
 
S

ShaneDevenshire

Hi,

The only problem with this solution is you can't put cell references within
{}. So if you want to use cell references here is one solution:

=SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($B$14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002))

Then the formula requires array entery - Shift+Ctrl+Enter
If the reference cell B1:B3 are switched to B1:D1 (a row) then

=SUMPRODUCT(($A$14:$A$30002=B1:D1)*($B$14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002))

without array entry.
 
S

Steven

Shane,

Thank you for your response.

Is there a limit of using only one TRANSPOSE in the formula. I have tried
with additional and I cannot make it work.

Steven
 
H

Harlan Grove

ShaneDevenshire said:
=SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($B$14:B$30002=$A$3)*
($C$14:$C$30002=$C$2)*($D$14:$D$30002))
....

Or as long as A14:A30002 don't contain wildcard characters,

=SUMPRODUCT(COUNTIF(B1:B3,$A$14:$A$30002)*($B$14:B$30002=$A$3)*
($C$14:$C$30002=$C$2)*($D$14:$D$30002))
 
R

RagDyeR

Try this ... needs only regular entry:

=SUMPRODUCT(($A$14:$A$30002=T(INDIRECT({"B1","B2","B3"})))
*($B$14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*$D$14:$D$30002)

Assumes values in B1, B2, and B3 are Text.
If they're numeric, change the "T" in front of Indirect to an "N".

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


One last question:

Instead of {"A","B","C"} is there a way to use cell references? I have
tried many things without sucess.

Thank you,

Steven
 

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

Similar Threads


Top