# sum rows based on sub-total

1. ## 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).

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.

2. ## 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.

3. ## 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. ## Re: sum rows based on sub-total

With a pivot table.

See the attached file.

5. ## 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. ## 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.

7. ## Re: sum rows based on sub-total

a pivot table with the running total.

see the attached file.

8. ## 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. ## 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. ## 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. ## 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. ## Re: sum rows based on sub-total

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

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

#### 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