SUMPRODUCT with multiple criterias and empty cells

Question

I have the following Excel spreadsheet:

       A          B               C         D        E        F         G
1               Profit           1.Sales  2.Sale   3.sale      
2   Product A     50              500       600       0      Product A   110
3   Product A     60                0       400       0      Product B   90  
4   Prodcut A     20                0         0       0      Product C   130
5   Product B     90              800         0       500
6   Product C     80                0         0       400
7   Product C     50              300       750       200
8   =""           =""             =""        =""       =""

In Column A different products are listed. It can happen that the same products appear several times in the list. In Column B you can find the budgeted profit for each product and in Columns C:E the different sales of each product.

In Column G the sum of the profit of each product is shown in case the product has any sales. For example Product A has no sale in Row 4; therefore the sum of its profit is B2+B3 = 110.

I use the following formula to get the sum of the profit:

G2 = SUMPRODUCT($B$2:$B$7*(($C$2:$C$7>0)+($D$2:$D$7>0)+($E$2:$E$7>0)>0)*($A$2:$A$7=F2))

This formula works perfectly so far.

However, now I want to expand this formula to Row 8. Row 8 does include =" " which causes a result of #VALUE! of the above formula.

How can I make this formula work despite the =" " in Row 8?

Answer 1

Try SUMPRODUCT() with two arguments:

=SUMPRODUCT($B$2:$B$8,(($C$2:$C$8>0)+($D$2:$D$8>0)+($E$2:$E$8>0)>0)*($A$2:$A$8=F2))