Forum Discussion
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 names and G3 is the cell reference for the sheet number.
I then use
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$5:$H$145&"'!d34:d35"),A2)) where $H$5:$H$145 is the range of worksheet names created by the command above.
cells d34 and d35 contain the information used to select the information to count, if it meets the criterion in column a.
I followed the instructions for an earlier version of Excel and this combination worked. Now the SUMPRODUCT equation returns #REF. What has happened and how do I fix it. Or where do I find the instructions for making it work now.
(BTW, this is the worksheet that counts the number of inurnments for a given year across 141 worksheets that have information about partners that have reserved a columbarium niche. The year of interest is given in Column A (such as A2) with the formula in B2.)
Thanks for any help.
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!
12 Replies
THANK YOU ALL! Enabling Excel 4.0 Macros allowed the list of worksheet names to populate correctly. At that point I could instruct the spreadsheet user to update the appropriate cell when a worksheet name is changed. With the cell updated, the list shows exact valid worksheet names, without any discrepancy, and the column with the SUMPRODUCT function works perfectly.
Why do you have 141 worksheets?! If they all follow the same basic structure -- and in my experience most folks who come here with that kind of many-worksheet-workbook DO retain the same structure in each of their many sheets --then you might well make your job a whole lot easier by combining them all into a single database.
If there's a separate sheet for year, or for zipcode, or for whatever...make that "whatever" a column of its own in the combined database. Excel does wonders at parsing out--separating--relevant data according to your need. Breaking things apart into multiple sheets--unless there's some truly compelling reason--just leaves you doing a lot of the heavy lifting that Excel's many database functions can handle quite readily.
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.
SheetNames:
Enable Excel 4.0 Macros in your copy of Excel following these instructions:
I can't find where SheetNames is defined and I can't figure out to redefine it.
I recently upgraded to Office2021 so things may not have come over when I moved files from my old computer
Thank you. When I changed the value in H43 manually, everything worked. What I found is that the lrvel command is returning an error. It doesn't seem to find the range SheetNames that is supposed to be a "range" across the sheet names for all the worksheets. How do I create the appropriate range that references all the worksheet names?
If the definition of SheetNames is using GET.WORKBOOK, then you have to be sure that Excel 4.0 Macros are enabled for it to work.
This page describes how to ensure they are enabled in your workbook.
How is SheetNames defined?
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!
