Nested array multiplication inside SUMIF function

Question

Hello.

Will appreciate any thoughts on the following situation. I am using a SUMIF function to sum those values in ARRAY2 which correspond to a CRITERIA being satisfied in ARRAY1. So the basic format of the function is SUMIF(ARRAY1, CRITERIA, ARRAY2). Pretty simple so far.

But I'm using the INDEX function to extract the arrays from a large TABLE, so now the formula looks as follows: SUMIF(INDEX(TABLE,, column_index1), CRITERIA, INDEX(TABLE,, column_index2)). So it starts to get a bit more complex at this point but it does work. I have used this multiple times while extracting different columns from the said table and using them as array inputs to the SUMIF function.

The problem I'm having is when I introduce the next level of complexity by doing an array multiplication within the SUMIF function as follows:

=SUMIF(INDEX(TABLE,, column_index1), CRITERIA, INDEX(TABLE,, column_index2)*INDEX(TABLE,, column_index3))

The above expression produces the following unhelpful error message -

There's a problem with this formula.

Not trying to type a formula?

When the first character is an equal ("=") or minus ("-") sign, Excel thinks it's a formula:

• you type:   =1+1, cell shows:   2

To get around this, type an apostrophe ( ' ) first:

• you type:   '=1+1, cell shows:   =1+1

I have tried the following thus far. Tried wrapping the bolded array multiplication sub-expression inside parentheses in an attempt to force it to resolve that sub-expression first, but no joy. Also tried placing the bolded array multiplication sub-expression in cell A1 (or any empty cell) to see if it resolves correctly outside the SUMIF function. This works as expected and produces a new dynamic array which is basically a row by row multiplication of the values in the two input arrays, which is what I'd expect. This resulting dynamic array is the same size as the two input arrays and spills as expected. If I then reference this resulting dynamic array as "A1#" and use this reference inside the above SUMIF function as follows, this also works and produces the mathematically correct result:

=SUMIF(INDEX(TABLE,, column_index1), CRITERIA, A1#)

So clearly, the array multiplication (by itself) works as expected. The SUMIF function (by itself) works as expected. But nesting the array multiplication inside the SUMIF function does not work. Any idea why? Is this due to some limitation of Excel that I'm unaware of? Or am I not formulating the nested formula correctly?

Will appreciate any and all insights. BTW, I'm using Excel 365 on a Mac.

Answer 1

The first and third arguments of SUMIF must be ranges, not arrays.

INDEX(TABLE,, column_index2) returns a range: the column in TABLE with the specified index.

But INDEX(TABLE,, column_index2)*INDEX(TABLE,, column_index3) is not a range, but an array.

So SUMIF fails.

Try

=SUMPRODUCT(INDEX(TABLE,, column_index2)*INDEX(TABLE,, column_index3)*(INDEX(TABLE,, column_index1)=CRITERIA))

Answer 2

Thank you! Your suggestion works perfectly. Seems I was conflating ranges and arrays.