Count Previous Occurrences, Column Formula

Options

I am attempting to count all previous occurrences of a row id for a sheet, as we continue to add rows through automated work flows (copy row). The formula I am using is simple and counts as expected. =COUNTIF($[ROW ID]1:[ROW ID]@row, [ROW ID]@row)

Since I am wanting to only count from a specific row up, I started the range at 1 and end it at the specific row desired. However this blocks being able to make it a column formula. The formula will also not populate when new rows are added via workflow.

Is anyone aware of a work around for this?

Answers

  • Adam Murphy
    Adam Murphy ✭✭✭✭
    Options

    Since you are saying the ROW ID is the same on multiple rows, am I right to assume this is not referring to the system autonumber field "Row ID"?

    If so, you should enable that(with a new name such as Row), then try =COUNTIFS([Row ID]:[Row ID], [Row ID]@row, Row:Row, <Row@row)

  • Wesley Walker
    Options

    Thank you for your response! We were using an auto number field for the ROW ID. The the reason it has multiple entries is because the data is being moved (copied) from one parent sheet where time is being logged for tasks, multiple times for each task. Since we were using copy row, it was creating multiple entries on the "child" sheet for each ROW ID on the "parent" sheet.

    Based off your advise, I created a unique identifier formula that will auto populate when new rows are added and that worked great!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!