# Specifying Named Ranges in Multiple Sheets in Single Wrkbook

#### kcleere1127

##### New Member
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.

Ken

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### XLGibbs

##### Well-known Member
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

##### New Member
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,018
Messages
5,834,973
Members
430,330
Latest member
drAli77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back