# Thread: Summing across multiple worksheets based on variable data

1. ## Summing across multiple worksheets based on variable data

I'm not sure what to call what I am trying to do. I'm sure it's not that hard, I just can't figure out how to do it.
I have a Workbook with multiple worksheets that I use to track my daily exercise routine. I have a worksheet for each month and then I recap it on one page. Pretty simple, all works well.

Columns included in the sheets are date, time of day, distance, elapsed time and shoes. What I would like to do now is to be able to include on the recap sheet how many miles I have on each pair of shoes. Basically, if Shoe A, add distance to Shoe A total. I'm thinking it is a Consolidate or Compile or Sum but I am just not sure how to set that up. If anyone can tell me or point me in the right direction, I would appreciate it. Thanks.

2. The attached works for three sheets. It's going to get to be a mess of a formula for 30 days (sheets).
I thought there would be an array formula that would do this, but haven't been able to construct one.
Might be best in VBA. I'm sure you'll get that solution shortly. :-)

Since posting this, I thought of another way using INDIRECT so you can fill it down. See the new upload.

3. ## The Following User Says Thank You to kweaver For This Useful Post:

donebb (2015-04-27)

4. Donebb,

It would be nice if you could just use Sumif() with a 3D formula. However, Sumif is NOT 3D enabled. So a work around is to place a summary area in the same place on each sheet which can use the Sumif to get the total by shoes then use a SUM with a 3D formula on your summary sheet to get the overall total. Sample attached.

Note: you can enter the summary section on all the sheets at once by selecting them (Hold Ctrl & click on each sheet) then just entering the data & formulas on one it will go on all!

Sample File: Donedd.xlsx

HTH

5. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

donebb (2015-04-27)

6. Good suggestion, RG.

If the OP doesn't want to do your suggestion, what do you think of mine w/the indirect?
I don't suspect his database will get too large, so this shouldn't be too costly in execution time and resources.

7. Kevin,

An interesting and innovative approach.

I'll be honest I'm not really sure how the your formula does what it does.
SumProduct has always given me problems although I can usually figure them out I don't try to create them.
I'm sure if I spent the time I could figure it out, maybe?

I'm definitely for anything that works, but when more than one thing works I like to stick with the simplest (like me) one so 6 months down the line I still can easily see what it does. Again, that's just me.

8. It would be much simpler to use one sheet for all the data instead of one sheet per month. It will make reporting far easier.

9. ## The Following User Says Thank You to rory For This Useful Post:

donebb (2015-04-27)

10. Hi RG/kweaver

Summing across sheets is pretty straightforward.

For example, if you had sheets [Jan] to [Dec], you could have a [Totals] sheet with a formula in say, cell [k2] as:
Code:
`=SUM(Jan:Dec!a20)`
This formula will sum the contents of cell [a20] on all of the sheets between [Jan] and [Dec].

So, to answer the poser's question, it would be simple to have a 'block' on each sheet which first summarizes the data on each sheet, using the SUMIF formula. Then, on the [Totals] sheet, we just refer to these computed values in those cells.
The attached file shows the example.

Note: It doesn't matter what the sheets are named between the start sheet [Jan] and the end sheet [Dec].
As you can see from my example attached, I didn't put in sheets for [Feb], [Mar] etc. to demonstrate this.

zeddy

11. ## The Following User Says Thank You to zeddy For This Useful Post:

donebb (2015-04-27)

12. Zeddy,

I believe that's what I did in post #3.

13. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

zeddy (2015-04-23)

14. Hi RG

Oops.
My eyes are bad!
I look at attached files when they are posted like in post#2
I have a blind spot when they are posted like in post#3.
But on a clear night I can see the moon.

zeddy

15. Zeddy,

No problemo, just though I'd give a friendly little jab. I've been known to do exactly the same thing!

16. Hi RG

Don't mention jabs.
I hate injections.

I deserve a swift kick in the ghoulies.

zeddy

17. Hi Guys,

Thank you all so much for your suggestions. I am not a spreadsheet expert but I am usually the go to guy at anyplace that I have worked. When people would ask me about setting up a spreadsheet for something, I would tell them to really think about what they want to get out of it. Don't just throw something together and then expect results.

So, what do I do on this little project. Well, you know.

I didn't really expect to keep using this spreadsheet for more than a month or two but I have found it to be very useful. I have used your ideas to make it more useful and simpler.

I consolidated the data. Instead of monthly worksheets, I now have Yearly ones. (Maybe later, I'll consolidate that too.) It was kind of an annoyance to setup a new worksheet each month. I used the SUMIF function to gather my information on shoe mileage. I liked the idea of INDIRECT but honestly I didn't understand it. Also, I have some worksheets that have other information, Weigh-In, Blood Pressure, etc. and I think the way it works is that you have to have the sheets laid out the same.

This new version looks like it will work very well. Thank you all for your help.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•