Expanded sumproduct

[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"},D1430002))

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

Thank you,

Steven

 

[Don Guillett]

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

 

[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

 

[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

 

[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)

 

[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

 

[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 B11 (a row) then

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

without array entry.

 

[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

 

[Harlan Grove]

=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))

 

[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

 

[T. Valko]

Am I missing something here?

What's wrong with ISNUMBER(MATCH(...))

 

[Steven]

Harland,

The countif is a WOW.

Thank you,

Steven