Formula to calculate project number based on group

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

2019-09-23 170742.png

Find more posts tagged with

Formulas

Comments

BernardW

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>

Paul Newcome

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.

Grace N

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

2019-09-24 101034.png

Grace N

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

Paul Newcome

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

Grace N

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

Paul Newcome

Sorry about that. Glad you were able to get it working. Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?