# Formula to calculate project number based on group

edited 12/09/19

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

Tags:

• ✭✭

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@rowDepartment@row) < 10, "0" + COUNTIFS(Department\$1:Department@rowDepartment@row), COUNTIFS(Department\$1:Department@rowDepartment@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@rowDepartment@row) < 10, "0" + COUNTIFS(Department\$1:Department@rowDepartment@row), COUNTIFS(Department\$1:Department@rowDepartment@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@rowDepartment@row) < 10, "0") + COUNTIFS(Department\$1:Department@rowDepartment@row).............

Which would leave you with:

=RIGHT(Department@row, 1) + "." + IF(COUNTIFS(Department\$1:Department@rowDepartment@row) < 10, "0") + COUNTIFS(Department\$1:Department@rowDepartment@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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!