Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    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. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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.
    Attached Files Attached Files
    Last edited by kweaver; 2015-04-22 at 17:54.

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

    donebb (2015-04-27)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    donebb (2015-04-27)

  6. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    donebb (2015-04-27)

  10. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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
    Attached Files Attached Files

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

    donebb (2015-04-27)

  12. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    I believe that's what I did in post #3.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    zeddy (2015-04-23)

  14. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    No problemo, just though I'd give a friendly little jab. I've been known to do exactly the same thing!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    Don't mention jabs.
    I hate injections.

    I deserve a swift kick in the ghoulies.

    zeddy

  17. #12
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    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
  •