Question: I'm having a fairly large dataset where I need to combine multiple entries into a single value. My dataset contains data on the combination of two datasets, each using their own ID's and keys.

I thought of using a `Sumproduct()` function like this:

`=SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2);--('Raw data'!O:O={20;21;22;23;40});'Raw data'!S:S)`

With `Landgebruik!A2` holding an ID for the first dataset, which I need to aggregate the second dataset to.

`'Raw data'!O:O` contains the ID's from the second dataset. In the case above I need to sum the area (in `'Raw data'!S:S`) when the value of the second ID is any of these values: `{20;21;22;23;40}`. (OR logic) The column only contains integer values.

Is there any other way of fixing this then duplicating `--('Raw data'!O:O=20)` for all values in the array?

**EDIT:**

I went with the work-around for now, which was: `=SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2);--('Raw data'!O:O=20)+('Raw data'!O:O=20)+('Raw data'!O:O=21)+('Raw data'!O:O=22)+('Raw data'!O:O=23)+('Raw data'!O:O=40);'Raw data'!S:S)`. But I feel that there should be a more elegant way of doing this.

Answer: 
You could make a small change to your current formula; change the `;` to `*` (`--` are also unneeded in that particular case):

```
=SUMPRODUCT(('Raw data'!C:C=Landgebruik!A2)*('Raw data'!O:O={20;21;22;23;40})*'Raw data'!S:S)

```

And that should work. 

---

When you feed separate parameters to `SUMPRODUCT`, each parameter has to be of the same size. But when you multiply them like this, it forces evaluation and the arrays expand. 

For example, if you take two arrays, 5x1 and 1x5, you get a 5x5 resulting array:

[![enter image description here](https://i.stack.imgur.com/JTzAs.png)](https://i.stack.imgur.com/JTzAs.png)
This could work:

```
={SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2);--IFERROR(MATCH('Raw data'!O:O;{20;21;22;23;40};0)>0;0);'Raw data'!S:S)}

```

This needs to be entered as an array formula.