SUMPRODUCT

[benjaminyaon]

Hi Guys , this is nothing to do with VBA, this is about the simple Excel formula.

please see attached, i try find out the total amount with all the product with the same code, the following formula is not working for me.

=+SUMPRODUCT((A:A="PWRBT")*(B:B)*(C:C))

Please help.
Thanks

[stanleydgrom]

benjaminyaon,

Sumproduct reqires a range like A3:A10, not A:A, similar to your formula in cell F12.

The formula in cell F8:

=SUMPRODUCT((A3:A10="PWRBT")*(B3:B10)*(C3:C10))


Have a great day,
Stan

[benjaminyaon]

benjaminyaon,

Sumproduct reqires a range like A3:A10, not A:A, similar to your formula in cell F12.

The formula in cell F8:

=SUMPRODUCT((A3:A10="PWRBT")*(B3:B10)*(C3:C10))


Have a great day,
Stan

Thx Stan, but i would like the sum for all product, if i use A3:A100, it doesn't work. please advise, thanks

[mbarron]

Your formula resulting in the #VALUE because of the RATE and COUNT text in rows 13, 24 etc... you cannot have text in your "multiplying" columns. Remove the RATE and COUNT from the lower tables and your formula will work.

[mbarron]

another option would by to use an array formula (confirm with ctrl+shift+enter):

=SUM(IF(A3:A100="PWRBT",B3:B100*C3:C100,0))

[Bob Phillips]

Maybe try

=SUMPRODUCT((OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)="PWRBT")
*(OFFSET($B$1,1,0,COUNTA($A:$A)-1,1))
*(OFFSET($C$1,1,0,COUNTA($A:$A)-1,1)))

[benjaminyaon]

Maybe try

=SUMPRODUCT((OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)="PWRBT")
*(OFFSET($B$1,1,0,COUNTA($A:$A)-1,1))
*(OFFSET($C$1,1,0,COUNTA($A:$A)-1,1)))


thanks, but this formula return #VALUE!

[benjaminyaon]

another option would by to use an array formula (confirm with ctrl+shift+enter):

=SUM(IF(A3:A100="PWRBT",B3:B100*C3:C100,0))


This formula working, Thanks so much for your help....

[Bob Phillips]

Then why didn't this one work

=SUMPRODUCT((A3:A10="PWRBT")*(B3:B10)*(C3:C10))

[mbarron]

Then why didn't this one work

=SUMPRODUCT((A3:A10="PWRBT")*(B3:B10)*(C3:C10))
It does work - it is in cell F12 in the workbook that was posted.

The formula you provided has the same issue that the =SUMPRODUCT((A3:A100="PWRBT")*(B3:B100)*(C3:C100)) version would have which is the text in the B and C columns cause the #VALUE result.

[Bob Phillips]

Ok, I didn't provide that formula, and I the workbook provided had whole columns, but I see that the headings cause that failure.

Anyway, you can use

=SUMPRODUCT(--(A3:A100="PWRBT"),B3:B100,C3:C100)

and avoid array formulas.