# [Calc] how to sumproduct with match condition?

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
``````

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 D2``

Yes 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.

1 Like

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)

2 Likes

Or, first extract the unique values from column A using the Standard filter and apply the formula to these values.

2 Likes

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!