Count values once no matter how often they appear

  • Thread starter Thread starter musha-bsuha
  • Start date Start date
M

musha-bsuha

I'm trying to sum data in Sheet1 from 50+ other sheets in the same file,
using =SUM(EUN1!AD8+EUN2!AD8+SV!AD8....and so on. However, I need to ammend
the formula every time new sheet is added.

Is there a formula which will automatically count the values in the
respective cell in all sheets, irrespectively how many are added?

Thanks a million!
 
Try the below..Sheet1 is the 1st sheet and Sheet3 is the last sheet...Try
inserting new sheets inbetween...Keep the first and last sheets names
constant...and always make sure to insert in between

=SUM(Sheet1:Sheet3!AD8)
 
One common way to address this is to add an empty sheet after Sheet1 and
before any of the sheets you want to sum. Name the new sheet FirstSht (for
example). Add another empty sheet after the last sheet you want to sum. Name
this new sheet LastSht. On Sheet1 use a SUM formula like

=SUM(FirstSht:LastSht!AD8)

FirstSht & LastSht don't affect the total because they are empty. If new
sheets are added anywhere between FirstSht & LastSht, they will automatically
be included in the SUM formula range.

Hope this helps,

Hutch
 
Create a sheet "sandwich".

Inset a new sheet to the immediate left of the first sheet you want to
include in the calculation and name it something like Start.

Inset a new sheet to the immediate right of the last sheet you want to
include in the calculation and name it something like End.

Then use a formula like this:

=SUM(Start:End!AD8)

When you create new sheets that you want to include in the calculation just
insert them between the Start sheet and the End sheet.
 
T. Valko said:
Create a sheet "sandwich".

Inset a new sheet to the immediate left of the first sheet you want to
include in the calculation and name it something like Start.

Inset a new sheet to the immediate right of the last sheet you want to
include in the calculation and name it something like End.

Then use a formula like this:

=SUM(Start:End!AD8)

When you create new sheets that you want to include in the calculation just
insert them between the Start sheet and the End sheet.
Click to expand...

I like to use "All" for the Start sheet name and "Sheets" for the End sheet
name. Then I hide them and you get a formula that looks like this:

=SUM(All:Sheets!A1)

As best I can tell, this will survive using "Move or Copy..." and selecting
"(move to end)".
 
Back
Top