Thread: Sumproduct #NA error with multiplication coersion

The following formula works flawlessly:
=SUMPRODUCT((ShiftSelectValues=Template!B7:O7)*ShiftHours)

Where shiftselectedvalues and shifthours are A2:A52 and C2:C52 respectively.

However if I change the matching criteria to use a column range instead of a row range, for example B2:B30, the calculation fails with #NA. With the evaluate formula tool it looks like matching is causes #NA rather then producing a true or false.

Anyone know why this is happening and how to fix it. Let me know if you need the spreadsheet. Thanks

Try:

=SUMPRODUCT((ShiftSelectValues=Template!B7:O7)*TRANSPOSE(ShiftHours))

confirmed with CTRL+SHIFT+ENTER not just ENTER

You need to transpose the ShiftHours to convert to row entries, ie. semicolon separated values as opposed to comma separated values

Isn't it the Template!B7:O7 range that you want to make into a column? If so try using this setup

=SUMPRODUCT(ISNUMBER(MATCH(ShiftSelectValues,Criteria_Range,0))+0,ShiftHours)

In that version Criteria_Range can be either a single column or a single row, it doesn't matter which

....or as long as the values in Criteria_Range don't repeat you can also use this version

SUMPRODUCT(SUMIF(ShiftSelectValues,Criteria_Range,ShiftHours))

I guess someone is looking over my shoulder.

Originally Posted by evilevil_monkey

how does the +0 cause the function to work.

This part

ISNUMBER(MATCH(ShiftSelectValues,Criteria_Range,0))

returns an array of TRUE/FALSE values depending on whether there is a match or not. SUMPRODUCT needs to work with numbers so +0 "co-erces" TRUE to 1 and FALSE to 0. You could use "implicit coercion" by multiplying the two conditions like this

=SUMPRODUCT(ISNUMBER(MATCH(ShiftSelectValues,Criteria_Range,0))*ShiftHours)

but it's arguably more inefficient that way.....and if ShiftHours contains any text values (even a "null string" "" returned by a formula) that will give you a #VALUE! error - the first version ignores text

As pointed out by daddylonglegs.. I misinterpreted the range transposition you were requesting....