sumproduct of numbers corresponding to criteria that are part of a list-CodePudding

I have an array of numbers, each number having a tag associated to it in a different column. I need to sum up the numbers in the array for which the tag is included in a different array that lists the tags which are allowed to be summed up.

=SUMPRODUCT(B1:B7, COUNTIF(A1:A7,A10:A12))

Here a simplified example:

The output should be 400 (since only Prod A and Prod B are the list a10:a12)

Any hint? Is in Google Sheet. Have tried to combine sumproduct and countif but problem with non matching array sizes. And am not a wiz... Thank you much in advance!

CodePudding user response：

You can use filter function to first filter the values from 1st array based on 2nd array. Then simply use SUM to sum it up

=SUM(FILTER(B1:B7,COUNTIF(E1:E2,A1:A7)))

This works in Google Sheet by default, and in Excel for O365 users

CodePudding user response：

Combine SUMPRODUCT with SUMIFS:

=SUMPRODUCT(SUMIFS($B$1:$B$7;$A$1:$A$7;$A$13:$A$15))

Notice the change if we replace C with Other:

UPDATE: Looks like this works just in Excel but not Google Sheets. For Google Sheets you need a different formula:

=SUMPRODUCT(--(IFERROR(MATCH($A$1:$A$7;$A$13:$A$15;0)>0;0));$B$1:$B$7)