Sumproduct certain rows

[Iulian Panosche]

Hello,

I have the following table:
A B C
Name Value1 Value 2
1 x 2 1
2 y 4 4
3 w 3 6
4 x 2 7
5 w 2 3
6 x 8 4
7 w 2 9

I need a formula to calculate sumproduct for each value in col A.
For ex: sumproduct for x should be 2*1+2*7+8*4=48
As the table is too big, i do not want to add a new column with the product of values in col B and C and then sumif. I'm hoping to find a formula to do that in a simpler way
so I could then have in a diff sheet only the sumproducts for x, y, w

Thank you

[Jonmo1]

Try

=SUMPRODUCT((A1:A7="x")*B1:B7*C1:C7)

[Fotis1991]

Hello,

As the table is too big, i do not want to add a new column with the product of values in col B and C and then sumif.
Thank you

If the table is too big it's better to use a helper column and then SUMIF. The calculations will be more fast.

[Jonmo1]

If the table is too big it's better to use a helper column and then SUMIF.

+1

Helper columns are NOT a bad thing.
Excel gives you 256 columns in XL2003, 16384 in XL2007+
Might as well use some of them.

[Iulian Panosche]

Example.xlsx

How come in the attached file it does not work?

[Fotis1991]

It's the worst idea to use whole columns reference for functions as SUPRODUCT(Even if this gives you right results!!)

Please Login or Register  to view this content.

[Jonmo1]

Because the ranges in C and D contain TEXT values (The headers in row 3)

Don't use entire column references, specify the exact row#s to be used.
=SUMPRODUCT((Data!B$4:B$10=B4)*Data!C$4:C$10*Data!D$4:D$10)

[Iulian Panosche]

Thank you all. Now it's ok.
You've been of great help

[Jonmo1]

You're welcome.

[Fotis1991]

You are welcome and thanks for the feedback.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.