Count IF as a Column Formula

Cesar Perez

I have a file that gets updated daily. There is a formula to identify unique items, in this case states. The file also has a formula to look for duplicate part numbers. The issue is that if the last row is a duplicate and the end users deletes the row, then the unique items formula disappears and no longer calculates on new rows. How can I make the unique items formula into a column formula?

=IF(COUNTIFS(State$1:State@row, State@row) = 1, COUNTIFS(State$1:State@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell)))))

The issue is the State$1 in the COUNTIFS statement.

https://app.smartsheet.com/b/publish?EQBCT=bab9fbcd059b4c2fbe5cdb0e6de673dc

Samuel Mueller

@Cesar Perez I actually have a solution for you, but it involves adding a couple columns..

the first column to add is an auto row column, call it somethiing like UniqueID

Call the second column something like RowID

in the RowID column you will put the column formula: =match(UniqueID@row, UniqueID:UniqueID, 0)

This will actually give you a reference to the row number. Then your actual formula will be something like

=IF(COUNTIFS(State:State, State@row, RowID:RowID, <= RowID@row) = 1, "Unique", "Duplicate")

@Genevieve P. ChatGPT is fun to work with! I actually have been using it to clean up my API code (i'm lazy). I think it is getting better with Smartsheet. Maybe you guys could make an AI community member using ChatGPT 😀

-also, I borrowed the Row ID idea from this post, and it has come in super handy in a lot of situations. Work Breakdown Structure (WBS) - Column Formula Configuration - Page 3 — Smartsheet Community

-additional info for Cesar, the Row ID won't populate until you hit save after adding new rows.

Samuel Mueller

@Cesar Perez Good afternoon Cesar, try the formula below

=IF(COUNTIFS(State:State, State@row) = 1, "Unique", "Duplicate")

You can modify the true and false (Unique, duplicate) values to meet your needs

Cesar Perez

Thank you @Samuel Mueller. Unfortunately that formula does not work. I need the numbers in the Unique State column to increase when they are unique. This is the formula I use to do that: =IF(COUNTIFS(State$1:State@row, State@row) = 1, COUNTIFS(State$1:State@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))). The issue is with the absolute reference State$1. Is there a workaround to count from row 1? This way I can convert the formula to a column formula.

Chris Shifflett

=IF(COUNTIFS(State$1:INDEX(State, ROW()), State) = 1, COUNTIFS(State$1:INDEX(State, ROW()), OR(ISBLANK(State), NOT(ISBLANK(State)))))

In this formula, the ROW function returns the current row number and the INDEX function returns the reference to the current row for the State column. So, it will now work for all rows, regardless of where the last row is.

Genevieve P.

Hiya! Just jumping in here to clarify - @Chris Shifflett it looks like you may be using ChatGPT to answer this question, is that correct? ChatGPT hasn't quite caught up on Smartsheet formulas yet, so it will suggest incorrect syntax. Smartsheet does not currently have a ROW function.

@Cesar Perez Your formula looks good! I would continue to use this.

Although you can't set it as a column formula because of the absolute reference, you can drag-fill the formula down the whole column. Then new rows inserted in the middle of your sheet or at the bottom will automatically bring that formula in anyway, based on the surrounding cells. (See: Use or Override Automatic Formatting and Formula Autofill)

Cheers,

Genevieve

Chris Shifflett

@Genevieve P. - I used my old formula sheet which was mixed up with other excel functions. I do apologize. Ahh!

Genevieve P.

Haha no problem! 🙂 Easy mixup!

Samuel Mueller

@Cesar Perez I actually have a solution for you, but it involves adding a couple columns..

the first column to add is an auto row column, call it somethiing like UniqueID

Call the second column something like RowID

in the RowID column you will put the column formula: =match(UniqueID@row, UniqueID:UniqueID, 0)

This will actually give you a reference to the row number. Then your actual formula will be something like

=IF(COUNTIFS(State:State, State@row, RowID:RowID, <= RowID@row) = 1, "Unique", "Duplicate")

@Genevieve P. ChatGPT is fun to work with! I actually have been using it to clean up my API code (i'm lazy). I think it is getting better with Smartsheet. Maybe you guys could make an AI community member using ChatGPT 😀

-also, I borrowed the Row ID idea from this post, and it has come in super handy in a lot of situations. Work Breakdown Structure (WBS) - Column Formula Configuration - Page 3 — Smartsheet Community

-additional info for Cesar, the Row ID won't populate until you hit save after adding new rows.

Cesar Perez

Thank you @Samuel Mueller. I used your suggested two helper columns to solve my problem. I modified the formula to generate the desired results and made it a column formula. This way even if the end user deletes the last row in the sheet the formula will still work. The new formula is:

=IF(COUNTIFS(State:State, State@row, RowID:RowID, <=RowID@row) = 1, RowID@row)

@Chris Shifflett, the issue with your comment is that if the end user deletes the last row in the sheet. Any new rows will be missing the formula in the Unique State column. This creates the issue of missing data in rollup sheets or reports.

@Genevieve P. the problem is that I would have to drag down the formula every time the end user deletes duplicate numbers in the file, specially if the last row is deleted. This becomes very maintenance heavy.

Samuel Mueller

Glad it worked for you!