# Sum Cells on Non-Contiguous Sheets

#### Claymationator

##### Well-known Member
I have a workbook that I am in the process of creating that will be used for planning/budgeting for a large number of stores. Each sheet will have an individual store on it where assumptions will be built in for the next year's plan. Each store has different build years, states, managers, region directors, etc. and I want to be able to aggregate at any of those levels (i.e. what is the total sales for stores built in 2010, or in NV?)

I know that I can get a total aggregate amount by selecting the first to the last sheet, but that will only work for the overall total...and if I choose a state, I need to be able to only add up those stores that fall in a particular state...same thing for region directors, etc (non-contiguous sheets.)

I want to be able to set it up so that I can choose the aggregate level from a dropdown in a summary sheet instead of having to create a vast number of sheets in the workbook that has the data already aggregated at every level.

One thought I had would be to put in a few variables into each sheet that highlights which groupings the particular store falls into. For example, a store in NV could have the following groupings listed in B1:B3

Build Year = 2010
Region Director = #3
State = NV

So, if I were to select 2010, #3, or NV that sheet would show up in the aggregated summary. However, if I were to choose a grouping other than one of those 3 it wouldn't show up in the total.

Basically the aggregate levels that I need won't always be on contiguous sheets, so I need to devise a formula or VBA that will allow me to get there.

Any help is appreciated.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I just did something similar and created a master worksheet that aggregated all of the individual worksheet data into transactional items (like you'd have in a database), then used Pivot Tables.

It took some VBA to aggregate it all, but not much, and once you have that you're golden.

HTH,

Thanks for the post Smitty. I have done that as well in the past, but I am no expert in VBA, and wondered about a formula method. I was able to come up with a solution to anyone that is interested.

I created a dynamic named range called SheetList that would return all of the matching sheets to aggregate based on the user selection, and then used this formula to return the sum of the applicable cells from all the sheets in the SheetList:

Code:
``=SUMPRODUCT((N(INDIRECT("'"&SheetList&"'!"&ADDRESS(ROW(),COLUMN())))))``

Since my summary sheet is set up the exact same way as the other individual sheets this worked well to get me the desired result.

Thank you. This helped me alot!

Regards,

M.

Replies
5
Views
388
Replies
0
Views
2K
Replies
5
Views
677
Replies
0
Views
382
Replies
1
Views
782

1,203,046
Messages
6,053,193
Members
444,645
Latest member
mee siam

### 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