Categories item number given
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 56 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.
Best Answer

Hi, @Twana Mohammed,
One way to do this is to use an autonumber column. The autonumber 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 3digits 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!
Answers

Hi, @Twana Mohammed,
One way to do this is to use an autonumber column. The autonumber 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 3digits 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!

Hello @Toufong Vang,
Thank you very much for this kind of support, it was really helpful and worked perfectly.
I appreciate your support.
Help Article Resources
Categories
Check out the Formula Handbook template!