0

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
6
  • 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,...?
    – fixer1234
    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.
    – Marcin
    Oct 26 '14 at 22:59
  • Can more than one sequential row have TOTAL in Col C?
    – fixer1234
    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.
    – Marcin
    Oct 26 '14 at 23:42
  • Darn, I had a simple solution if there were no sequential TOTALs. Back to the drawing board.
    – fixer1234
    Oct 26 '14 at 23:44
3

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.

16
  • 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...
    – Marcin
    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 =]
    – Tyson
    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.
    – Marcin
    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.
    – fixer1234
    Oct 27 '14 at 1:00
  • IFBLANK(C1,"",formula) did not work it says "Unknown function: "IFBLANK" (google spreadsheet).
    – Marcin
    Oct 27 '14 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.