Forum Discussion
SUMPRODUCT ERROR
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!
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.