sum of cells between empty cells on varying size cell groups

Chupis

New Member
Joined
Jan 25, 2005
Messages
16
I have a workbook thousands of records long. I am using subtotal (count) to divide the data into categories and provide count subtotals on two of the columns. My problem is I need to do a "sum" subtotal on another column using the same line as the other subtotals.

my limitations: I cannot use a macro (this file is replaced/configured daily) and I cannot add another line between cell groups (which is what doing a subtotal(count) will do).

I guess my question is how can I sum cells between empty cells all the way down to my last record? Keep in mind all my cell groups vary in length.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Re: sum of cells between empty cells on varying size cell gr

Have you tried using Data / Subtotals instead? You sort the data by category, and then for every change in category you can summarise. Tick both COUNT and SUM. When you've printed the report, remove subtotals to allow new data to be added, then repeat the process for the next report.
Down the track, you can create some VBA to automate it.

Denis
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Re: sum of cells between empty cells on varying size cell gr

You can't copy the original data, paste the values to another sheet, and use a pivot table? That would seem to be the easiest solution.
 

Chupis

New Member
Joined
Jan 25, 2005
Messages
16
Re: sum of cells between empty cells on varying size cell gr

I did use Data/Subtotals for the counts. I do not see where I can select both a count and a sum though. I am using office 2003.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Re: sum of cells between empty cells on varying size cell gr

Hmmm... should have looked at the dialog. Only one operation allowed, on multiple fields -- not the other way round.
OK, go with Tazguy's suggestion but you can simplify the update process by creating a dynamic range from the data, and using the range name as the data source for the pivot table. Check out this post for more info. To automatically update the pivot table when the user switches to the sheet, look here

Denis
 

Chupis

New Member
Joined
Jan 25, 2005
Messages
16
Re: sum of cells between empty cells on varying size cell gr

Thank You all who responded!

I actually found a very simple solution to my problem!

This is what I did; I ran a subtotal(count) on all the columns I needed (including the ones I needed to sum). I then only selected the columns that needed to be summed and did a find/replace. I looked for "(3," and replaced it with "(9," , that geve me a sum totals for the varying cell groups on the same line as the count totals. I hope this makes sense...

Thank you again to all who responded.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,482
Members
414,144
Latest member
UDFNewbie

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
Top