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 split it into two SUMIFS as mentioned in the comment. If all values are integers, then comparing 'Raw data'!O:O to 20,21,22 and 23 is the same as testing it for >=20 and <=23. The value 40 has to be done separately.

```
=SUMIFS('Raw Data'!S:S,'Raw Data'!C:C,Landgebruik!A2,'Raw Data'!O:O,">="&20,'Raw Data'!O:O,"<="&23)
+SUMIFS('Raw Data'!S:S,'Raw Data'!C:C,Landgebruik!A2,'Raw Data'!O:O,40)

```

in my locale

or

```
=SUMIFS('Raw Data'!S:S;'Raw Data'!C:C;Landgebruik!A2;'Raw Data'!O:O;">="&20;'Raw Data'!O:O;"<="&23)
+SUMIFS('Raw Data'!S:S;'Raw Data'!C:C;Landgebruik!A2;'Raw Data'!O:O;40)

```

in your locale.

This only works when several of the criteria are consecutive integers.

**Speed considerations**

SUMIFS is thought to be about five times faster than sumproduct so may be the preferred option for large datasets [as demonstrated here](https://exceloffthegrid.com/optimize-formula-calculation-speed-part-8-sum-with-multiple-criteria/)

You could argue that the more general suggestion of (effectively) five SUMIFS within a SUM from @ BrakNicku should be about as fast as one SUMPRODUCT, but the SUM(SUMIFS) would probably still win because formulas like SUMIFS handle full-column references more efficiently than array formulas.
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.