CODEWITHSUNDEEP
excel
yaugenka
yaugenka

Reputation: 2871

Sumproduct matching values in excel

I have two excel tables:

    A       B       C       D       E
1   John    10              Mark    2
2   Tommy   20              Tommy   3
3   Jane    15              John    4
4                           Kate    2
5                           Jane    1

Is there a function to sumproduct values in colum B with those values in column E which match by name, i.e. 10*4 + 20*3 + 15*1 ?

Upvotes: 1

Views: 111

Answers (2)

JNevill
JNevill

Reputation: 50273

You can use sumif for this and just sum up the results when you are done:

=B1 * sumif(D:D, A1, E:E)

Copy that down your sheet, and then add up the totals.

If you don't want a ton of formulas hanging out on your sheet, you could convert this to a CSE/Array formula:

=SUM($B$1:$B$3*SUMIF(D:D, $A$1:$A$3,E:E ))

Just enter that in and hit Ctrl+Shift+Enter to enter it. It will get curly braces around it, which means it's an Array formula.

Upvotes: 2

Forward Ed
Forward Ed

Reputation: 9894

Since you asked about sumproduct, we could use SUMPRODUCT

=SUMPRODUCT(($A$1:$A$5=A1)*$B$1:$B$5)*SUMPRODUCT(($D$1:$D$5=A1)*$E$1:$E$5)

Now that is assuming there are no repeats (all names are unique). In the event that names are not unique you will have those numbers added together then multiplied.

After you apply that to a column and copied down appropriately, lets say F1 to F3, in F5 you could get your final answer using:

=SUM(F1:F3)

Upvotes: 0

Related Questions