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

user_4945888 0
Viewed 87

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:

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!

sumproduct countif google-sheets excel
3 Answers

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

Sample Screenshot

user_69082820

Combine SUMPRODUCT with SUMIFS:

enter image description here

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

Notice the change if we replace C with Other:

enter image description here

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)

enter image description here

user_91998280

You can use sumproduct function.

=sumproduct(($B$1:$B$7)*($A$1:$A$7=A10))

Drag this down to get desired answers.

user_199382760
Related