Categories item number given

Twana Mohammed

Hello Dears,

I hope my email finds you well, I have seen your videos about Smartsheet and they are accommodating, I am stuck in a function I need for my Smartsheet if you can support me.

The idea is, I have a category and in this category, I have 5-6 items and need a function that shows me in each column next to the category the number of items is added, for example, I have IT, Safety, Furniture, and Generator.

If I have chosen IT in the first row, it gives the number 001.

If in the second row I have Chosen Safety it gives the number 001.

if in the third row I have chosen again IT it gives the number 002. so on

Do you think this will be possible?

I have attached an image of the sample and I have used this Function but it is not working:

=IF([BPA Type]@row = "IT", "Safety", "Branding", "Furniture", "Generator", "Home Appliance", COUNTIFS([BPA Type]$1:[BPA Type]2, ISNUMBER(@cell)) + 1)

Your support is highly appreciated, thank you in advance.

TVang

Hi, @Twana Mohammed,

One way to do this is to use an auto-number column. The auto-number will make each row unique. It also let's you know the row's sequential order when you evaluate it using BPA Type. In the image below, this "helper" column is labeled RowNum.

The expression below will return the number of rows that have the same BPA Type as the current row, and where the value in RowNum is less than or equal to that of the current row.

COUNTIFS([BPA Type]:[BPA Type], [BPA Type]@row, RowNum:RowNum, <=RowNum@row)

If your PO numbers are 3-digits long, then you can prepend "00" to the number and then take the RIGHT() 3 characters.

=RIGHT( "00" + COUNTIFS([BPA Type]:[BPA Type], [BPA Type]@row, RowNum:RowNum, <=RowNum@row) , 3)

Cheers!