How can I make COUNTIF into a column formula?

Kelsee Katsanes · 05/15/24

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!

Jason P · 05/15/24

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"), "")

Kelsee Katsanes · 05/15/24

@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 · 05/15/24

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?

Jgorsich · 05/16/24

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

https://community.smartsheet.com/discussion/comment/436362#Comment_436362

Mark.poole · 05/16/24

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

Kelsee Katsanes · 05/16/24

https://community.smartsheet.com/discussion/comment/436393#Comment_436393

Exactly

Mark.poole · 05/16/24

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.

How can I make COUNTIF into a column formula?

Answers

Help Article Resources

Categories