Sumif Across multiple Worksheets using Partial Tab Name

Good afternoon,

I'm trying to create a formula that will look at the name in a cell (from a dropdown list) and sum all worksheets that use that same name. For example all the tabs are named Division 1a, Division 1b and Division 1c, etc up through 23 divisions all with an a-b-c tab. The formula will need to look at the cell with the dropdown name and sum one cell in all three tabs of that same division.

Thank you so much for any help you can provide.

Create named range "Sheets" in 3 cells containing (say A11:A13)

=$I$4&"a"
=$I$4&"b"
=$I$4&"c"

in H10

=SUMPRODUCT(SUM(INDIRECT("'"&Sheets&"'!F13")))

see attached.

Thanks John,

I looked at what you had done, however this is only summing up the Division 1 tabs. How do I then link this to Cell I4 so that when the division changes in I4, the total will change for only that new division?

This is now perfect. Thank you so much.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.