Nested if & countifs to generate a prefix based on number of occurrences

Paul Newcome

You have it set as View Only so I cannot access the formula.

s.anders

Sorry Paul - can you tell I'm new at this! https://app.smartsheet.com/b/publish?EQBCT=b42a98bd5885437b8a967cd0a12dfed7

Paul Newcome

This looks like the issue part:

COUNTIF(($[Supervisor forename]$1:[Supervisor forename]@row) + ([Supervisor forename]$1:[Supervisor forename]@row), ([Supervisor forename]@row + [Supervisor surname]@row))

What exactly are you trying to accomplish with that?

s.anders

I'm trying to work out whether in the supervisor forename and surname rows (which is the first 4 characters of the identifier e.g. JADO) there is a repeat of the same e.g. "JADO" and if so, to place a lowercase b, c, d,..., etc. to signify it is the second, third, fourth etc. project of the same supervisor. Over years, there would be many projects for the same supervisor and so it needs to be clear how many they have supervised.

Paul Newcome

I think I see what's going on. Check out the sheet now. You are going to need an auto-number column with no special formatting, and I dropped a new formula into another column.

s.anders

Okay thanks, what do I put into the auto-number column?

Paul Newcome

The auto-number column will self-populate when you save the sheet.

s.anders

Okay. Have done that but its not generated the prefix in the identifier formula. I also noticed that the date is now wrong - its reading the day and year, rather than month and year. I'm based in the UK and so have my date column set to dd/mm/yy but maybe you have mm/dd/yy?

Paul Newcome

The formula I put in does not reference the day at all. It is

sup forename / sup surname / stud forename / stud surname / counter based on number of times sup is in that year / . / year

The names are grabbing first two characters and year is grabbing last two character.

s.anders

okay, the original also had month too but I can that back in. The prefix still isn't showing though, if you can help on that?

Paul Newcome

I thought the portion where you have the month was the counter. That's where I placed the counter in my formula. I have adjusted it.