+ Reply to Thread
Results 1 to 12 of 12

sum rows based on sub-total

  1. #1
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    sum rows based on sub-total

    Hello Excel Gurus,

    I'm looking for a formula or vba code that will calculate a total (or sub-total to be more precise) for rows that are constantly changing (i.e. dynamic range).

    I have attached a spreadsheet for more information which is like a payroll time sheet.

    In it, I have to go to each 'sub-total' row to calculate the sum for the activity for that particular location. As there are hundreds/thousands of rows I have to repeatedly hit the sum function for every sub-total line.

    I've currently done this in cells:

    F9 and G9
    F20 and G20
    F24 and G24
    F32 AND G32

    I'm wondering if there is an easier way to calculate all the rows that have a sub-total as an identifier. Either in the form of a formula/vba-code?

    Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: sum rows based on sub-total

    Could you use a pivot table to do this? If you get rid of all of the empty rows and subtotal rows, a pivot table can group all of the locations and activities in column A together and give you all of the subtotals and grand totals you want.
    Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.

    <---Click * Add Reputation for all helpful comments. It's like giving a smile.
    Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.

  3. #3
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    Re: sum rows based on sub-total

    Unfortunately pivot table will not work in this situation as the report has the sub-total rows in it already but it does not calculate the totals so I have to manually sum the rows. Pivot table could work but then that would require me to delete all the sub-total rows and cleaning a lot of other data on the sheet which will take too much time.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: sum rows based on sub-total

    With a pivot table.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: sum rows based on sub-total

    You could do a new column H with the following formula to sum the regular totals. You can edit it to do the overtime totals as well.
    Please Login or Register  to view this content.
    I know it is annoying to make another column, but it may be a lot faster than doing it by hand in column F and G.

  6. #6
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    Re: sum rows based on sub-total

    When I drag down the formula in Column H as you suggested, the solution works well for the first sub-total row but for every consecutive sub-total row it subtracts the two columns from each other and
    results in a running-total.

    I would require only a total for each sub-total line not the running total of each columns. So the sub-total I'm looking for is in Column F and G.

    See attached.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: sum rows based on sub-total

    a pivot table with the running total.

    see the attached file.

  8. #8
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: sum rows based on sub-total

    @Jack When copying the formula down I assumed the subtotal rows were already blank. You will need to remove any of the subtotals you already have in there for mine to work or the value will be counted twice.

  9. #9
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    Re: sum rows based on sub-total

    nigelbloomy that solution works great thank you so much! oeldere, i would definitely prefer using pivot-table any time but a formula is more suitable for this problem as the data is not in a clean data set and there would be a lot of deleting/cleaning required in order for it to work in a pivot table. That's not to say it can't, it's just it would take too long so formula works better.

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: sum rows based on sub-total

    Please Login or Register  to view this content.
    As you can see in my examples, I don't have problems creating a pivot table (with NO clean data).

  11. #11
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: sum rows based on sub-total

    Here's a quick one-off method
    Filter and delete all rows with Sub-total in column A
    Highlight the entire range of data, click Subtotal on the Data Tab
    Check both columns F and G (Regular total and Overtime total)
    Click OK

  12. #12
    Registered User
    Join Date
    05-01-2015
    Location
    Vancouver, BC
    MS-Off Ver
    2010
    Posts
    35

    Re: sum rows based on sub-total

    Thanks for your help folks. I think all methods suggested work now. Really appreciate it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. randomized rows based on percentage of total rows
    By Uwantachat in forum Excel Programming / VBA / Macros
    Replies: 35
    Last Post: 03-22-2021, 01:52 PM
  2. Replies: 1
    Last Post: 05-13-2015, 12:52 PM
  3. Delete Duplicate Rows based on one duplicating value- and keep total at the top the same.
    By jeremynorthwestern in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-21-2014, 01:32 AM
  4. Replies: 1
    Last Post: 01-30-2013, 12:12 PM
  5. [SOLVED] Copy data to different sheet based on filter and add total rows
    By ravibemail in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2012, 07:25 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1