Search code examples
excelexcel-formulaworksheet-functionsumproduct

Using OR logic on an array as argument in Sumproduct

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.

Solution

  • 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