How can I make COUNTIF into a column formula?

Options

I have been using a COUNTIF formula that I am unable to convert to a column formula. Is there a way that I can alter it so it can be a column formula. This is what I have:

And this is the message that pops up when I try to convert it to a column formula:

I just don't want to continue to drag the formula down with each submission. Any help would be greatly appreciated!

Answers

  • Jason P
    Jason P ✭✭✭✭
    Options

    HI @Kelsee Katsanes

    As noted here https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas?source=apphelpicon the absolute reference $1 is not supported in a column formula.

    I'll be upfront here I'm no formula guru, I'm simply looking at one we have that is simular. Would this work for you?. =COUNTIF([LPS Function]@row, "Content Management") Same again but where there is no result cell will be blank. =IF(COUNTIF([LPS Function]@row, "Content Management") > 0, COUNTIF([LPS Function]@row, "Content Management"), "")

    Cheers.

    Forever forwards Backwards never.

  • Kelsee Katsanes
    Options

    @Jason P

    I was able to make it a column formula, but unfortunately it changed my request IDs in the process.

    See below:

    Where before it was LPS_CM_1 and LPS_CM_2.

  • Jason P
    Jason P ✭✭✭✭
    Options

    Revert back to your original until sorted, as I say I'm no guru but. So that others can look and offer advice what's the goal / scenario of the entire formula, can you share non sensitive and formulas in all column here?

    Cheers.

    Forever forwards Backwards never.

  • Jgorsich
    Jgorsich ✭✭✭
    Options

    I think this was inadvertently asked twice. Here is the other thread:

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    Options

    What exactly are you wanting it to do. A Sequential count of Content Management?

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭
    edited 05/16/24
    Options

    All of this is basically the same information that @Jgorsich shared. With some additional functions added in to better customize it for you.

    You need an auto number of some kind. I use the created date. How ever if information is put in at the same time I would not recommend that. If that’s the case create an auto number row. And depending on if new entries are entered at top or bottom the formula would change very slightly.

    If entries are entered from the top.

    =COUNTIFS(Auto:Auto, >Auto@row, [LPS Function]:[LPS Function], =[LPS Function]@row) + 1)

    If at the bottom

    =COUNTIFS(Auto:Auto, <Auto@row, [LPS Function]:[LPS Function], =[LPS Function]@row) + 1)

    Both of these will do sequential counts for each item. if you want it only for Content Management then create an if statement. This formula will put blanks for the rows that are not "Content Management"

    =if([LPS Function]@row="Content Management",COUNTIFS(Auto:Auto, >Auto@row, [LPS Function]:[LPS Function], ="Content Management") + 1)

    At which point you can recreate the formula for each separate column.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!