Sequential numbering with Subtotals

[Guest]

I have a large number of subtotals in a spreadsheet. I need to add a column
and have sequential numbers added.

How can I sequentially number on the subtotaled rows, not the ones inbetween
which are hidden?

 

[T. Valko]

Maybe something like this:

Assume columns A and B contain the data that's subtotaled.

B6 is the first subtotal and A6 contains something like: X Total.

Enter this formula in C6 (assumes column C is empty):

=IF(ISNUMBER(SEARCH("total",A6)),MAX(C$5:C5)+1,"")

Copy down as needed.

This will also number the "Grand Total" at the bottom of the data. If you
don't want that included just delete the formula from the corresponding cell
in column C.

Biff

 

[Dave Peterson]

I use Sum in my subtotal and my key column was column A.

I used this formula in another column (row 2) and dragged down:
=IF(COUNTIF(A2,"*total*")=0,"",COUNTIF(A$2:A2,"*total*"))

If you used a different function, adjust the "*total*" to match.


Adjust the column to match your subtotal column.
Adjust the row to match that first

 

[Guest]

I use Sum in my subtotal and my key column was column A.

I used this formula in another column (row 2) and dragged down:
=IF(COUNTIF(A2,"*total*")=0,"",COUNTIF(A$2:A2,"*total*"))

If you used a different function, adjust the "*total*" to match.


Adjust the column to match your subtotal column.
Adjust the row to match that first

Thanks, I'll test it tomorrow at work.