How can I make COUNTIF into a column formula?

Kelsee Katsanes

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 like the current formula because it allows me to set up my Request ID like this:

Whereas, changing the formula to this =COUNTIF([LPS Function]:[LPS Function], "Content Management"), makes my Request ID's look like this:

I want to be able to use the formula that I currently have and be able to convert it to a column formula. With the way it currently is, I have to drag the formula down with every submission. There has to be a way or a workaround to fix this. I've looked at other topics/questions and can't seem to find anything that will help this issue. Any help would be greatly appreciated!

Jgorsich

I feel your pain - I'm a big fan of using the same technique in Excel and finding a work around was a big priority for me. Luckily, I've got one for you!

First, you'll need to add some kind of counter column - search for any answer that includes "row@row" and you'll find different ways to do it. Once you've got that, you can change your countif to a countifs and do this:

=countifs(LPS Function]:[LPS Function], "Content Management",row:row,<= row@row)

Basically ends up doing the same thing but fits inside of the fairly draconian column formula rules.

Jgorsich

I feel your pain - I'm a big fan of using the same technique in Excel and finding a work around was a big priority for me. Luckily, I've got one for you!

First, you'll need to add some kind of counter column - search for any answer that includes "row@row" and you'll find different ways to do it. Once you've got that, you can change your countif to a countifs and do this:

=countifs(LPS Function]:[LPS Function], "Content Management",row:row,<= row@row)

Basically ends up doing the same thing but fits inside of the fairly draconian column formula rules.

bisaacs

Hey @Kelsee Katsanes!

Here's the snip from "Create and Edit Column formulas" indicating if column formulas support the reference type:

Unfortunately because it has an absolute reference, it doesn't appear this can be converted to a column formula. My next suggestion was to maybe create a workflow that changes the cell value to the formula, but it looks like the automation wraps the formula in '', which isn't helpful!

It does look like if you insert a row between two cells that have the formula, it does appear the formula stays in the column in the new row, so maybe look at doing something like that?

Kelsee Katsanes

@Jgorsich,

Can you show me what you mean by the counter column?

Jgorsich

@Kelsee Katsanes sure - check out @Paul Newcome's answer here on how to make a "row" column:

Is there a way to reference the row above?

Amanda Merrigan

Aug 13, 2021

I'm trying to mark active child tasks in an overall task. I'd like do =IF(Done@row-1 = 1, 1, 0) in the Active Task column, but we often insert tasks and I don't want to put a hard @row number in there that would need to be maintained when new tasks are added. Same for automatic numbers in a RowID column.

I was also doing this based on status, but we have several subtasks that have the same status and we only want to add additional status' as a last resort.

Does anyone know of a way to do this with Ancestors or a helper column to show when the task above is complete and that is now the active task?

https://us.v-cdn.net/6031209/uploads/AOIDN8Q8YW17/image.png

---

View Post

Add that to your sheet and the formula I wrote should work for you :).

Kelsee Katsanes

Jgorsich

May 16, 2024

I feel your pain - I'm a big fan of using the same technique in Excel and finding a work around was a big priority for me. Luckily, I've got one for you!

First, you'll need to add some kind of counter column - search for any answer that includes "row@row" and you'll find different ways to do it. Once you've got that, you can change your countif to a countifs and do this:

=countifs(LPS Function]:[LPS Function], "Content Management",row:row,<= row@row)

Basically ends up doing the same thing but fits inside of the fairly draconian column formula rules.

It worked!! Thank you thank!!!

Jgorsich

Glad I could help :).