Let’s say I have 3 columns (A, B, C) and 10 rows (1 to 10). I want to SUMPRODUCT the values of the cells from columns B and C where values of column A match.
For example:
bla | 2 | 3
abc | 1 | 4
bla | 6 | 8
adb | 1 | 3
adb | 2 | 5
I want to have in D1 the SUMPRODUCT: 2*3 + 6*8; and 1*3 + 2*5 in D2
Hello,
Isn’t this:
I want to have in D1 the SUMPRODUCT: 2*3 + 1*3; and 1*3 + 2*5 in D2
supposed to be:
I want to have in D1 the SUMPRODUCT: 2*3 + 6*8; and 1*3 + 2*5 in D2Yes lol, I’ll edit it
No, it’s wrong again. It seems to be 2*3+6*8=54 in D1 and D3, 1*4=4 in D2 and 1*3+2*5=13 in D4 and D5.
so, how can I do it so it only shows the matches once? (in this example, only D1 for bla and D2 for abc and D3 for abd but nothing on D4 and D5)
Or, first extract the unique values from column A using the Standard filter and apply the formula to these values.
Sure, that’s easier, but I think I prefer not to depend on filters, so I don’t have to update them every time I add more data.
but keep an eye to the size of your tables. Every change needs an recalculation of every sumproduct, every “filter” and every countif in every line, so a single change in on field results in huge recalculations. There can be delays for bigger tables …
You’re right, I’ll keep it in mind, thank you!
