I'd like this formula =IF(C1="TOTAL",COUNTIF(C$1:C1,"TOTAL"),ROW(A1)-COUNTIF(C$1:C1,"TOTAL")) to be automatically populated into a new row entry/ when a new row is added as explained in the question titled: "Auto Sequential numbering based on cell criteria - create two independent automatic numbering sequences"

Since I cannot keep any more rows than I need, if an automatic solution could be devised, please share (even using a different fourmula, arrayformula?).

  • What have you tried? Is it necessary to be populated "live" when needed or could entries be pre-populated and "hidden" until used?
    – fixer1234
    Oct 30 '14 at 5:09
  • I thought this looked familiar :-) I recognize it from your other question but nobody else will be familiar with the history. It is likely to get downvoted with no additional information. Make it a complete, stand-alone question with a little explanation, your constraints, what you've considered and tried, etc. Mention the issue with the array formulas.
    – fixer1234
    Oct 30 '14 at 5:53

Format your spreadsheet as a table. Excel will apply a formula to an entire column (within the table) including whenever you add a row. Click on Insert and then Table.

  • Is it possible to avoid making changes to how the spreadsheet is formatted? The spreadsheet receives data from other apps via APIs, changes are triggered automatically and changing the formatting might result in errors.
    – Marcin
    Oct 31 '14 at 1:11
  • I'm not sure what you mean. Other than row highlights, formatting has to be consistent in each column. When rows are inserted in a table, the new rows have the same formatting as those above and below them. Of course, rows can't be inserted at the end of the table.
    – BillDOe
    Oct 31 '14 at 5:28

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.