Specifying Named Ranges in Multiple Sheets in Single Wrkbook

[kcleere1127]

In the formula below you see I am adding up the instances where:

Atlanta_Inst="Complete"
and
Atlanta_Int="Complete"
When
AtlantaWho="N"

=SUMPRODUCT(('1'!Atlanta_Inst="Complete")*('1'!Atlanta_Int="Complete")*('1'!AtlantaWho="N"))

This formula refers to sheet 1 ('1'!) of a workbook. This workbook has 5 worksheets and I want to add total all the "N"'s in all 5 worksheets when as the conditions are met.

I know I could do this to get it to add the ranges in the 1st, 2nd, 3rd, 4th and 5th sheets:

=SUMPRODUCT(('1'!Atlanta_Inst="Complete")*('1'!Atlanta_Int="Complete")*('1'!AtlantaWho="N"))+SUMPRODUCT(('2'!Atlanta_Inst="Complete")*('2'!Atlanta_Int="Complete")*('2'!AtlantaWho="N"))

What I'd like to know is if there is a way to reference the multiple worksheets without adding more +SUMPRODUCT statements. Such as (just so you know what I'm thinking:

=SUMPRODUCT(('1'!'2'!'3'!'4'!'5'!Atlanta_Inst="Complete")*('1'!'2'!'3'!'4'!'5'!Atlanta_Int="Complete")*('1'!'2'!'3'!'4'!'5'!AtlantaWho="N"))

I'm sure what I did there is probably improper but I hope it tells you that I simply want to reference the ranges in the 5 sheets with a shorter formula.

Thanks in advance for any advise.

Ken

 

[XLGibbs]

Why not simply have the same sumproduct formula on each of the five sheets and aggregate them with a simple Sum formula at your desired result location.

Otherwise,I am unaware of a formula that will look on each sheet for 1 named range, as that would certainly cause issues (it would likely be some variation of a circular reference of some kind anyway, since each of the sheets has identical names)

 

[kcleere1127]

Thanks for the input. Excel does in fact allow the same range name across multiple worksheets and it differntiates with by the sheet numbers or whatever you actually name the tabs.

As far as putting my formula in each sheet...I'd consider that but I'd actually have to have 28 formulas on each of the 5 sheets as thats how many sections per sheet there are. That and inexperienced users will be entering data into the sheets so I want to keep the intelligence iand maintenance on one locked summary tab.

Thanks again for the input.