SUMIF formula over multiple worksheets not working like it should

[cwbasset]

Okay, I've really done it this time.

The sumif formula that I believe "should" be working for me is returning a #REF! error.

I need to pull data from 18 other tabs. I created a named range for those tab names and used this formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&All_Tabs&"'!$B$55:$B$110"),$B57,INDIRECT("'"&All_Tabs&"'!$d$55:$d$110")))

What am I missing?

 

[jasonb75]

Is 'All_Tabs' a valid named range containing a list of the sheet names in the workbook?
Do all of the sheets in that list exist?
Are there any blank / empty cells in the named range?
Are the sheets in the same workbook as the formula? If not, is the other workbook open?

 

[cwbasset]

Yes. I created a list of all the tab names ranging from "Apr_20" through "Dec_21".
Yes. All of the sheets in the list exist, and there are no blanks or empty cells in the named range.
All of the sheets are in the same workbook as well as the "ALL_Months" tab which is were I am trying to apply the formula.

Also, I did not include parenthesis when listing the tab names. I just added them in to my explanation to highlight the exact text of the labels.

 

[jasonb75]

Yes. I created a list of all the tab names ranging from "Apr_20" through "Dec_21".

This might be where the problem lies, if you have proper dates in that format then they will not be valid as sheet names, they need to be text strings otherwise the formula will be looking for sheets where the name matches the date serial number, not the format of the date.

Either format the cells as text then re-enter the dates, or use the text function in an adjacent range and name that for use in the formula instead.

 

[cwbasset]

Probably should have mentioned that the "Name List" cells are formatted as text and were originally typed, not converted from a date.

 

[Dave Patton]

check your formula, you may be missing a few characters

possibly =SUMPRODUCT(SUMIF(INDIRECT("'"&All_Tabs&"'!&"$B$55:$B$110"),$B57,INDIRECT("'"&All_Tabs&"'!&"$D$55:$D$110")))

 

[Marcelo Branco]

Maybe there are extraneous spaces on the tab list.
See if this works
=SUMPRODUCT(SUMIF(INDIRECT("'"&TRIM(All_Tabs)&"'!$B$55:$B$110"),$B57,INDIRECT("'"&TRIM(All_Tabs)&"'!$d$55:$d$110")))

M.

 

[jasonb75]

you may be missing a few characters

I think your suggestion has a few too many characters. The OP's original formula syntax is correct.

In addition to @Marcelo Branco's observation, are there any merged cells in the ranges that the formula refers to? I would have expected a different error if that was the case, but trying to cover all options.

 

[cwbasset]

No, that's not working. It doesn't like the extra quotation marks

There are also no merged cells in the column ranges.

BTW-- I really do appreciate all the help in trying to get this resolved!!!!

 

[Marcelo Branco]

No, that's not working. It doesn't like the extra quotation marks

Your formula looks perfect to me - I don't see any extra quotation mark.

Maybe there are one or more wrong names on the list. To check, in an empty column, try
=INDIRECT("'"&INDEX(All_Tabs,ROWS(A$1:A1))&"'!B55")
copy down

M.