total question

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
Hi All,

If I have heaps of sheets with values in the same position on each sheet is there a way to tally them all on another sheet, the catch is, can I specify which sheets to add.

EG, if I have Sheet 1 all the way through to Sheet 100, and Sheet SUMMARY will be the totaling sheet can i somehow choose to total only sheets 10 to 20 and 35 to 47 and 91 to 99 (for example).

I realise on a smaller scale I could use sumif but I know it only nests to 7 or so.

Awaiting any help.

Thanks :)
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Well, you could do something like...

Code:
=SUM('10:20'!A1)+SUM('35:47'!A1)SUM('91:99'!A1)

And you're not limited to the number of SUM()'s you can add to a formula.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,909
Office Version
  1. 2010
Platform
  1. Windows
you can cycle throuhg all sheets and then filter out the ones you dont want...



Code:
Sub TryThis()
    Dim mySheet As Worksheet
    For Each mySheet In ActiveWorkbook
        if mySheet.Name   'fulfills criteria  then
            sheets("Summary").cells( put your cell here ).value=mysheet.cells( put your cell here ).value
        end if
    Next
End Sub
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
you can cycle throuhg all sheets and then filter out the ones you dont want...



Code:
Sub TryThis()
    Dim mySheet As Worksheet
    For Each mySheet In ActiveWorkbook
        if mySheet.Name   'fulfills criteria  then
            sheets("Summary").cells( put your cell here ).value=mysheet.cells( put your cell here ).value
        end if
    Next
End Sub

If the values are in the same cell addresses this would overwrite the data. If in different cells, it could scatter as (I'm guessing :confused: ) undesired. I'm not sure VBA is even required (I posted a formula solution, but unsure if it fills the OP's request or not). And if it can be done via formulas, I'd opt not for VBA anyway. :cool:
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585

ADVERTISEMENT

Thanks for the speedy replies.

I will examine these and get back to you :)
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
OK, I am trying to adapt the formula.

My scenario is now as follows.

I have sheets named NOV 2008, DEC 2008, JAN 2009, FEB 2009, etc
I have a sheet called SUMMARY
I have a sheet called SUMMARY CONFIGURATION

My plan is to have a list on the SUMMARY CONFIGURATION sheet in ColA of all the sheets (NOV 2008, DEC 2008, JAN 2009, FEB 2009, etc). A user will be able to place a 1 in ColB next any month/year sheet name.

The SUMMARY sheet will then add up all values in, for example, Range C4 of the sheets that have had a 1 put next to them and place that tally in C4.

I hope that makes sense as it is actually quite tricky to explain.

Thanks :)
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585

ADVERTISEMENT

So Zack, your formula essentially works but I will never know what combination of sheets will be selected by a user to tally......that's what I have to figure out :)
 

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
Another question I have is can the formula --> =SUM('NOV 2008:FEB 2009'!B4) be feed by results from other cells.

So if for example the result of a formula in A1 was NOV 2008 and the result of B1 was FEB 2009 could I substitute A1 and B1 somehow into the formula somehow.

The formula for summing same range across sheets works for me but I need it to be dynamic in the sheet name part of the formula.

Thanks
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
OK, I am trying to adapt the formula.

My scenario is now as follows.

I have sheets named NOV 2008, DEC 2008, JAN 2009, FEB 2009, etc
I have a sheet called SUMMARY
I have a sheet called SUMMARY CONFIGURATION

My plan is to have a list on the SUMMARY CONFIGURATION sheet in ColA of all the sheets (NOV 2008, DEC 2008, JAN 2009, FEB 2009, etc). A user will be able to place a 1 in ColB next any month/year sheet name.

The SUMMARY sheet will then add up all values in, for example, Range C4 of the sheets that have had a 1 put next to them and place that tally in C4.

I hope that makes sense as it is actually quite tricky to explain.

Thanks :)

Try...

=SUMPRODUCT((THREED('NOV 2008:FEB 2009'!C4)),('SUMMARY CONFIGURATION'!$B$2:$B$5))

THREED is a function from the free morefunc.xll add-in.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top