Need help to auto populate a formula to display a number

2»

Answers

  • StacyW1006
    edited 04/19/24

    =+IF([Run type]@row ="E", +IF(COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))<10,0)+ COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row)) +IF([Run type]@row ="EH", "0")


    I

    AM DOING SOMETHING WRONG

  • KPH
    KPH ✭✭✭✭✭✭

    The + is used to join all the parts together:

    ="thing 1"+"thing 2"

    would be

    thing 1thing 2

    You do not need it at the very start of the formula

    You start with = and then each part of the formula is added with +. If you want to test out each part without the rest so you can see what is going on then swap the + for an =. You don't need both.

    Try this to see the sequence number on its own.

    =IF([Run type]@row ="E", +IF(COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row))<10,0)+ COUNTIFS([Row ID]:[Row ID], @cell <= [Row ID]@row, [Created (Auto)]:[Created (Auto)], WEEKNUMBER(@cell) = WEEKNUMBER([Created (Auto)]@row), [Created (Auto)]:[Created (Auto)], YEAR(@cell) = YEAR([Created (Auto)]@row)) +IF([Run type]@row ="EH", "0")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!