sum of cells between empty cells on varying size cell groups

[Chupis]

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.

 

[SydneyGeek]

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]

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]

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]

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]

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.