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 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.
Best Answer
-
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!
Answers
-
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!
-
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 60 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 40 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!