SumProduct Different Sum Range Based on Criteria

[neb255]

Hi,

Im working in Excel 2016. I am looking to find out if it is possible to designate different sum ranges for one specific criteria in a sumproduct formula while the rest of the conditions apply to both.

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Color[/TD]
[TD]Month[/TD]
[TD]ForSale[/TD]
[TD]AM1[/TD]
[TD]Am2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Black[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]50[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]100[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Black[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]75[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]25[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Black[/TD]
[TD]1[/TD]
[TD]N[/TD]
[TD]100[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]75[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Black[/TD]
[TD]2[/TD]
[TD]Y[/TD]
[TD]50[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Blue[/TD]
[TD]2[/TD]
[TD]N[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

In the above table we want the sum of all Black or Blue where Month = 1 and ForSale = Y. The only variable is that for Black we want to return AM1 while Blue should return AM2.

So at its most basic this would be

SUMPRODUCT(($A$2:$A$9="Black")*($B$2:$B$9=1)*($C$2:$C$9="Y")*($D$2:$D$9))+SUMPRODUCT(($A$2:$A$9="Blue")*($B$2:$B$9=1)*($C$2:$C$9="Y")*($E$2:$E$9))

The idea here would be to avoid writing the whole sum product twice and then adding them since this would require a second iteration.

Thanks in advance for any help

Cheers!

 

[DanteAmor]

Try this

=SUMPRODUCT((A2:A9={"Black","Blue"})*(B2:B9=1)*(C2:C9="Y")*(D2:E9))

 

[neb255]

Try this

=SUMPRODUCT((A2:A9={"Black","Blue"})*(B2:B9=1)*(C2:C9="Y")*(D2:E9))

Thanks that worked for my above question, I realize however as I'm re-reading my post that I asked the question incorrectly. It should have been Black is from AM1 and any other color (so <> Black) is from AM2.

 

[neb255]

I realize as I'm re-reading my post that I asked the question incorrectly. It should have been Black is from AM1 and any other color (so <> Black) is from AM2. so the original code would have been

[COLOR=#333333]SUMPRODUCT(($A$2:$A$9="Black")*($B$2:$B$9=1)*($C$2:$C$9="Y")*($D$2:$D$9))+SUMPRODUCT(($A$2:$A$9<>"Black")*($B$2:$B$9=1)*($C$2:$C$9="Y")*($E$2:$E$9))[/COLOR]

I found a possible answer which was

[COLOR=#333333]SUMPRODUCT(($B$2:$B$9=1)*($C$2:$C$9="Y")*(([/COLOR][COLOR=#333333]($A$2:$A$9="Black")*[/COLOR][COLOR=#333333]($D$2:$D$9))+(($A$2:$A$9<>"Black")*($E$2:$E$9))))[/COLOR]

 

[DanteAmor]

Great if the formula gets the expected value