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.

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(.......................)......

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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!