Using OR logic on an array as argument in Sumproduct

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 1

You can use text search for this:

--NOT(ISERROR(FIND('Raw data'!O:O,"2021222340")))

But you have to be careful that a shorter ID is not found incorrectly in a longer ID, e.g. if you want to search among the IDs { 123, 456, 789 } then 12 is not considered to be among the IDs. So a simple text search like the above would not work. You need a delimiter character to break up the string of IDs. Usually I use the pipe character for this purpose, since I cannot remember any case when it occurred in the original text of an Excel file, and because it makes the formula human-readable:

--NOT(ISERROR(FIND("|"&'Raw data'!O:O&"|","|20|21|22|23|40|")))

Examples:

'Raw data'!O:O is 20 => |21| is found in |20|21|22|23|40|

'Raw data'!O:O is 2 => |2| is not found in |20|21|22|23|40|

(If your IDs may include the pipe character, then you can use CHR(1), a long forgotten ASCII code for SOH meaning start of header; of course, it's less readable.)

The whole formula:

=SUMPRODUCT(--('Raw data'!C:C=Landgebruik!A2),--NOT(ISERROR(FIND("|"&'Raw data'!O:O&"|","|20|21|22|23|40|"))),'Raw data'!S:S)

(Sorry, my Excel uses , instead of ;)