Formula to calculate project number based on group
Hi
I want to calculate a project number based on what group it is in. In this example, there are 16 projects among 4 departments. I entered the 3rd column manually; i.e. the first digit is the department number and the second digit is the nth project for that department. What formula can I use to calculate the values in the 3rd column?
Thanks
Grace
Comments

Does the project number have to indicate how many projects there are in a department? If so, why? If not, I would use the auto number functionality and prefix/append it with the department and formatting to obtain the desired results. For example: <Dept#><auto num>

You would first need to establish which department is which department number. If you are actually using "Department #", then you can use a RIGHT function.
=RIGHT(Department@row, 1)
.
If you are using actual department names such as "Admin", "Production", etc, you will want to write a nested IF statement to generate the appropriate number. For example...
=IF(Department@row = "Admin", "1", IF(Department@row = "Production", "2", ...............................))))
.
NOTE: I intentionally put quotes around the numbers in the IF statement and left the RIGHT function as is to generate a text value as opposed to a numerical value.
.
Next we will add in the ".".
=first_formula_choice + "."
.
Then we add the count for the number of projects for that department.
first_formula_choice + "." + COUNTIFS(Department$1:Department@row, Department@row)
.
NOTE: The $ before the row reference to lock in row 1 as the start of your range.
.
Let me know how that works out for you.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Thanks, Paul. Your formula works for me.
I have a follow up question. I want to have 2 digits to the right of the decimal point. I modified your formula as follows:
=RIGHT(Department24, 1) + "." + IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0" + COUNTIFS(Department$1:Department@row, Department@row), COUNTIFS(Department$1:Department@row, Department@row))
Is there a more elegant solution?
Grace

Oops, my formula should be
=RIGHT(Department@row, 1) + "." + IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0" + COUNTIFS(Department$1:Department@row, Department@row), COUNTIFS(Department$1:Department@row, Department@row))

There isn't much in the way of "elegance" that can be added other than simplifying the IF(COUNTIFS portion just a little.
Think of it this way...
If you leave the third portion of an IF statement empty, then you would have a potential of two different results. Either the specified result if true, or blank.
.....................IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0" + COUNTIFS(Department$1:Department@row, Department@row), COUNTIFS(Department$1:Department@row, Department@row)..............
You can actually use an IF statement to say if the COUNTIFS is less than 10, "0". If it's true, then you get a 0. If it isn't, it leaves a blank. Then you add your COUNTIFS regardless of what it is.
.....................IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0") + COUNTIFS(Department$1:Department@row, Department@row).............
Which would leave you with:
=RIGHT(Department@row, 1) + "." + IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0") + COUNTIFS(Department$1:Department@row, Department@row))
.
Saves a few keystrokes but does the same exact thing.
I use this idea frequently with a nested IF to account for numbers under 10000.
......IF(COUNTIFS(.......................) < 10, "000", IF(COUNTIFS(.......................) < 100, "00", IF(COUNTIFS(.......................) < 1000, "0"))) + COUNTIFS(.......................)......
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Thanks again, Paul
I got an INCORRECT ARGUMENT error due to an extra ")" in your formula. This is the corrected formula
=RIGHT(Department@row, 1) + "." + IF(COUNTIFS(Department$1:Department@row, Department@row) < 10, "0") + COUNTIFS(Department$1:Department@row, Department@row)
Grace

Sorry about that. Glad you were able to get it working. Happy to help!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 438 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 451 Show & Tell
 30 Member Spotlight
 1 SmartStories
 282 Events
 32 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!