Forum Discussion
Solved
SUMPRODUCT includes non-numeric values
I have a somewhat complex SUMPRODUCT formula with INDIRECT worksheet and cell references (in yellow-highlighted C11 on the LF worksheet) that seems to be including text values even though the help sa...
scheij I added this to the end of the formula:
*ISNUMBER(INDIRECT("'"&$L$2&"'!"&$R$2))
Basically what I believe is happening is that the comparison is happening first and any text is > a number and hence you are getting "True" for each of those comparisons.
You may want to re-evaluate the rest of the formula keeping this in mind to make sure you don't have a similar error elsewhere in that monster equation.
BTW: have you considered defining NAMES for those ranges instead of all those INDIRECTs?
IMHO, simple INDEX/(MATCH on column names) could return columns, INDIRECT is not required.
I would go with that or, these days, XLOOKUP. It still leaves a problem when the data sheet is deleted though.