Forum Discussion
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 says that SUMPRODUCT treats non-numeric array entries as if they were zeros. If the "-" entries in Data!$X$2:$X$5000 were treated as zeros, the expected result should be 208. Any suggestions would be appreciated. Thanks.
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?
6 Replies
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?
Thanks, your addition to the formula worked! Regarding your suggestion about substituting all those INDIRECTs with NAMES for those ranges, could I still delete and replace the 'Data! worksheet, edit the value in 'LF!$L$2, and avoid #REF! errors? If so, I would certainly prefer to clean up these formulas, but the 'Data! worksheet must be changed often.
I haven't used a direct cell reference for about 8 years now but, then again, deleting the worksheet with the source data is not a use case I have considered to date. As an aside, even with your strategy for referencing data, it is still possible to let the named formula 'length' refer to
=INDIRECT("'" & L2 & "'!" & R2)
That would make the final formulae more readable but still leave you with volatile functions and over-size arrays.
Have you tried using PowerQuery to import your data from the csv file? That would allow you to move from one dataset to another without deleting sheets and breaking formulae. It would also provide structured references that precisely capture the size of the data table dynamically. The file size could also be reduced by only importing the fields you wish to use.
