Auto Sequential numbering based on cell criteria - create two independent automatic numbering sequences

How to make the numbering in column A skip any rows that have the Word "TOTAL" in column C but pick right up again in the next rows? At the same time can the rows with the word "TOTAL" in column C have their own numbering?

This should be achieved with a single formula and continue down the column AUTOMATICALLY with new appropriate numbers appearing down the column A when new rows are created / column C is updated.

I tried (using the answer given below) =IF(C1="TOTAL",COUNTIF(C\$1:C1,"TOTAL"),ROW(A1)-COUNTIF(C\$1:C1,"TOTAL")) it works but it does not automatically propagate in column A.

As an example if some rows contain "TOTAL" the column A would be:

Col A   Col C
1        S
2        S
1      TOTAL
3        S
2      TOTAL
3      TOTAL
4        S
5        S
4      TOTAL
• You want to intermix the numbers in the same column? If rows 3 and 5 have "TOTAL" in col C, the sequence going down col A would be: 1, 2, 1, 3, 2, 4? You don't want col A to be: 1, 2, blank, 3, blank, 4, and col B to be: blank, blank, 1, blank, 2, blank,...? Oct 26 '14 at 22:54
• If rows 3 and 5 have "TOTAL" in col C, the sequence going down col A would be: 1, 2, 1, 3, 2, 4. I tried to attach an image but the low reputation prevented me from doing so, sorry. Oct 26 '14 at 22:59
• Can more than one sequential row have TOTAL in Col C? Oct 26 '14 at 23:17
• Yes, TOTAL in column C will appear one after another, and the other value might also appear in several rows one after another. Oct 26 '14 at 23:42
• Darn, I had a simple solution if there were no sequential TOTALs. Back to the drawing board. Oct 26 '14 at 23:44

Here's one solution. It assumes the data starts in row 1 (if not, offset the row references). The formula in A1:

=IF(C1="TOTAL",COUNTIF(C\$1:C1,"TOTAL"),ROW(A1)-COUNTIF(C\$1:C1,"TOTAL"))

Copy that down column A for the whole data range.

Update: This will consolidate discussion from the comments into the answer. The question seeks to have values in col A appear automatically as new entries are added to col C. This can be done by adding an ISBLANK test to the formula and prepopulating col A:

=IF(ISBLANK(C1),"",IF(C1="TOTAL",COUNTIF(C\$1:C1,"TOTAL"),ROW(A1)-COUNTIF(C\$1:C1,"TOTAL")))

This can be copied to an arbitrarily large number of cells in col A. As long as there is nothing in col C for a row, the formula will return a null placeholder and the cell in col A will be blank. When a value is entered in col C, the formula will work as before.

• Thank you fixer1234, that does it beautifully but I'd also like it to continue down the column automatically for every new row... I tried the array formula but without success... Oct 27 '14 at 0:42
• @MarcinLachner if you can explain what you mean by that, I'm sure there is a solution, what i see is a formula that can be continued all the way down the column =] Oct 27 '14 at 0:55
• Sorry Tyson, I didn't make myself clear: I'd like the A column to update with the correct number when a new row in the column C gets an input (eg. "TOTAL"). Currently we have a semi-manual arrangement by which one needs to drag the formula down the column if there is a new row. Oct 27 '14 at 1:00
• There are a couple of ways to do that. The simplest is just to pre-populate way more rows than you need. Stick the entire formula (excluding the equal sign) inside: IFBLANK(C1,"",formula) (that's for A1; copy the extended formula down column A forever). The extra entries will be blank until something goes into Column C for that row. If your data ends up in more rows than you've copied the formula, copy any cell in column A and paste for another few hundred rows. Otherwise, you probably need a VBA program. Oct 27 '14 at 1:00
• IFBLANK(C1,"",formula) did not work it says "Unknown function: "IFBLANK" (google spreadsheet). Oct 27 '14 at 1:16