total question

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
591
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
Thanks for the speedy replies.

I will examine these and get back to you :)
 
Upvote 0
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 :)
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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
Back
Top