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

Question

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

Solution

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.

Comments

[fixer1234]

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,...?

[Marcin]

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.

[Marcin]

Yes, TOTAL in column C will appear one after another, and the other value might also appear in several rows one after another.

[fixer1234]

Darn, I had a simple solution if there were no sequential TOTALs. Back to the drawing board.

[fixer1234]

I added the example to your question and then updated it for the condition of repeated "TOTAL"s. I forgot to update the example description. To make it consistent and avoid confusion for someone else who might have a solution for you, you might want to edit your question to read: ...suppose C3, C5, C6, and C9 contain "TOTAL"...

[Marcin]

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

[Tyson]

@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 =]

[Marcin]

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.

[fixer1234]

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.

[Marcin]

IFBLANK(C1,"",formula) did not work it says "Unknown function: "IFBLANK" (google spreadsheet).

[fixer1234]

My bad. =IF(ISBLANK(C1),"",formula)

[Marcin]

I tried =IF(ISBLANK(C1),"",formula) but it works only for A1 and does not propagate. Are there any other suitable functions for copying that formula down the column, please?

[fixer1234]

There is no reason why that shouldn't propagate. The first 2 cells should look like: A1: =IF(ISBLANK(C1),"",IF(C1="TOTAL",COUNTIF(C$1:C1,"TOTAL"),ROW‌​(A1)-COUNTIF(C$1:C1,‌​"TOTAL"))) A2: =IF(ISBLANK(C2),"",IF(C2="TOTAL",COUNTIF(C$1:C2,"TOTAL"),ROW‌​(A2)-COUNTIF(C$1:C2,‌​"TOTAL"))) Are you getting something else?

[Marcin]

Yes, I'm getting exactly what you wrote above. The issue is that to get that I need to either copy the formula or drag it down the column. It does not re-apply itself automatically to the next cell down when new rows are created. Which part in our formula should make it propagate automatically? I tried "ARRAYFORMULA" but it wouldn't work.

[fixer1234]

That's the point of the ISBLANK. You don't paste it just for the existing rows. Paste it for 1,000 extra rows, or any arbitrary number that is way more than you need. It then just sits there, hidden, until there are entries to use it.

[Marcin]

Since I cannot keep any more rows than I need, if an automatic solution (maybe with a different formula) came to you, please share. Anyway ISBLANK is still a neat solution and thank you!

[fixer1234]

The formula isn't the problem, just the mechanics of putting it in the sheet. Usually, extra, hidden rows are not an issue. If you can clarify what makes this a problem, it might help to identify a solution. VBA could be used.

[Marcin]

I have around 30 columns of data in my spreadsheet. I use multiple formulas (most of which are 'arrayformula'), leaving extra rows for IFBLANK makes the array formulas continue down those columns indefinitely resulting in FALSE in multiple columns.

[fixer1234]

Only the result column should contain extra formulas. It shouldn't necessarily affect arrays in the other columns. However, if you are convinced that it is a requirement, I think you will probably need a VBA program to plug the formula into new cells as required. In that case, you don't need the IFBLANK add-on, just the formula in the answer. The VBA program is really a separate question. If you need help with that aspect, post a new question specifically about a VBA program to recognize a new row entry and populate a formula into a cell.

[Marcin]

I didn't manage to make it automatic but copying down the column is ok, thank you! So I have a column full of the correct data- sometimes though there is a need to ignore some rows. Is it possible to ignore some rows and continue the numbering? I explained what I mean at the end of the question above.

[fixer1234]

You can do pretty much anything if there is a defined rule. Questions on this site are intended to be single problems with applicable solutions to facilitate use by others. Implementing a different rule requires a different solution, which makes it a different problem. Create a new question that stands entirely on its own in terms of providing all of the information someone would need to solve it (don't just say it's like this problem except...), and include a reference to this question as one that is similar (explain what is different so it isn't flagged as a duplicate). Define the rules.