Forum Discussion
SUMPRODUCT includes non-numeric values
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?
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.
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.
PeterBartholomew1 That is exactly the idea I was suggesting. I did it for all the indirect ranges so the formula:
=SUMPRODUCT((INDIRECT("'"&$L$2&"'!"&$P$2)=C$4)*(INDIRECT("'"&$L$2&"'!"&$N$2)=$C$2)*(INDIRECT("'"&$L$2&"'!"&$O$2)=$C$3)*(INDIRECT("'"&$L$2&"'!"&$M$2)=$C$1)*(INDIRECT("'"&$L$2&"'!"&$Q$2))*(INDIRECT("'"&$L$2&"'!"&$R$2)>=VLOOKUP(C$4,Table1,3,FALSE))*ISNUMBER(INDIRECT("'"&$L$2&"'!"&$R$2)))becomes:
=SUMPRODUCT((Species=C$4)*(Gear=$C$2)*(MarkFound=$C$3)*(SurveySeqNo=$C$1)*(Number)*(Length>=VLOOKUP(C$4,Table1,3,FALSE))*ISNUMBER(Length))see attached. I did it for the specific highlighted cell, you can copy it to the rest 🙂
