CODEWITHSUNDEEP
excel-formula
Michi
Michi

Reputation: 5471

SUMPRODUCT with empty cells / ISNUMBER

I have the following Excel spreadsheet:

        A               B                c            D
1   Products           Sales 
2   Product A            50          Product A       #VALUE!
3   Product A            60          Product B
4   Product A            20          Product C
5   Product B            90
6   Product C            80
7   Product C            50
8   =""                =""

In Column A is a list of products and their corresponding sales are listed in Column B. In Column D I want to show the sum of the sales by using the following formula:

D2 = SUMPRODUCT(($B$2:$B$8)*($A$2:$A$8=C2))

Instead of showing me the result of 130 (B2+B3+B4) I get an #VALUE! error which is caused by the =" " in Row 8.

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

I tried to go with this way but it still shows an error:

D2 = SUMPRODUCT(($B$2:$B$8)*($A$2:$A$8=C2)*ISNUMBER($A$2:$A$8)*ISNUMBER($B$2:$B$8))

Please keep the following in mind: I cannot switch the SUMPRODUCT formula to a SUMIF(S) formula since my original file is more complex then the simple example above.

Upvotes: 1

Views: 2056

Answers (1)

XOR LX
XOR LX

Reputation: 7742

Use the 'native' form (i.e. with comma-separated arguments) of SUMPRODUCT, rather than the 'product' form, since text entries passed to the former are ignored.

=SUMPRODUCT($B$2:$B$8,0+($A$2:$A$8=C2))

I am slightly concerned about your statement "I cannot switch the SUMPRODUCT formula to a SUMIF(S) formula since my original file is more complex then the simple example above.". If the example you gave is not representative of your actual set-up, then it may be that a switch to the 'native' form of SUMPRODUCT is not actually possible (one advantage of the 'product' form is that it allows the processing of two-dimensional arrays, something which is not permissible within the 'native' form.)

Regards

Upvotes: 1

Related Questions