SUMPRODUCT with SUMIF

2
votes

I currently have this:

=SUMPRODUCT(SUMIF(A:A;Index(List;;1);B:B))

Column A is a list of names, column B is a list of values for each names. The named range List has 2 columns, the first one are names and the second one are boolean values (0 or 1).

My formula actually works to return the sum of every column B values of its corresponding name in the A column IF that name is in the first column of my named range List. It works fine.

However, I would like to filter that to only include names from List that have the boolean value equal to 1 (ie. Index(List;;2) = 1.

How is this possible?

This is what I tried but it gives me a #REF! error:

=SUMPRODUCT(SUMIF(A:A;Index(List;;1)*Index(List;;2);B:B))

2
excelexcel-formulaexcel-2010
When you replaced the commas with semi-colons, I take it you took note of the TWO commas after List? - XOR LX
Yes, but my problem seems to be with my List. If I do =INDEX(LIST;60;1) everything is fine, but as soon as I try another column (replacing the 1 with 2 for example), i get a #REF! error. List is actually =$B$2:$B$100;$L$2:$W$100 (this is from another sheet, column B being uniques names and all the other columns L:W being 0's or 1's). I thought it would do the same as the B2:W100 range without the C:K columns but it doesn't seem so. - dan

2 Answers

3
votes

Probably best to switch to an array formula**:

=SUM(SUMIF(A:A,IF(INDEX(List,,2),INDEX(List,,1)),B:B))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

1
votes

Since your Named Range consists of discontinous ranges, when INDEXing it you will need to include INDEX's 4th parameter (area_num) in order to clarify which of the ranges ($B$2:$B$100 or $L$2:$W$100) you wish to refer to, e.g.:

=INDEX(List,60,1,**2**)

which will return the value in cell L61.

Regards