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:
<tbody>
</tbody>
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
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!
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:
| A | B | C | D | E | |
| 1 | Color | Month | ForSale | AM1 | Am2 |
| 2 | Black | 1 | Y | 50 | 100 |
| 3 | Blue | 1 | Y | 100 | 50 |
| 4 | Black | 1 | N | 75 | 100 |
| 5 | Blue | 1 | N | 25 | 75 |
| 6 | Black | 1 | N | 100 | 25 |
| 7 | Blue | 1 | Y | 75 | 100 |
| 8 | Black | 2 | Y | 50 | 150 |
| 9 | Blue | 2 | N | 100 | 100 |
<tbody>
</tbody>
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
Code:
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!
