Forum Discussion
Solved
SUMPRODUCT ERROR
I am trying to count the number of entries that meet a given criterion across 141 worksheets. I first use =@INDEX(SheetNames,G3) to retrieve each sheet name where SheetNames is the range of sheet n...
The formula syntax is correct, and it returns the correct result in a small test workbook that I created.
Make sure that EVERY cell in H5:H145 contains a valid worksheet name. If there is only one discrepancy, the formula will return #REF!
Are you trying to build a 3D reference dynamically? Generally I don't understand your question.
Can you edit it and add some images? the @ operator when applied to an array result will return the first element in that array. So your lrvel formula will only ever return one value. Is that what you want?
What is the definition of SheetNames? Is it like this?
Generally I would advise using:
=LET(
data, VSTACK(<<3D_REFERENCE_OF_ALL_141_SHEETS>>),
filtered, FILTER(data, countif_filter_condition),
calc, SUMPRODUCT(COUNT(filtered)),
calc
)
But without more detail in the question, I can't offer much more than that.