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

s.anders

I am trying to get this part of a concatenate to work but keep getting #unparseable. The latter part of the concatenate works and generates a unique identifier for the project. However, the beginning section is meant to generate a prefix based on whether this project is the first, second (b), third (c) etc. project with the same lead person. The concatenate works in sheets but when transferring to Smartsheets no longer works. Below is this beginning prefix part, any help with finding the problem would be great!

=IF(COUNTIFS(($[Supervisor forename]$1:[Supervisor forename]@row),[Supervisor forename]@row,($[Supervisor surname]$1:[Supervisor surname]@row),[Supervisor surname]@row)>1,CHAR(96)+COUNTIFS(($[Supervisor forename]$1:[Supervisor forename]@row),[Supervisor forename]@row,($[Supervisor surname]$1:[Supervisor surname]@row),[Supervisor surname]@row)-COUNTIF(($[Supervisor forename]$1:[Supervisor forename]@row)&([Supervisor forename]$1:[Supervisor forename]@row),([Supervisor forename]@row&[Supervisor surname]@row)),””)

The original google sheets part of the concatenate is:

=CONCATENATE( IF(COUNTIFS($C$2:C2, C2, $D$2:D2, D2) > 1, CHAR(96 + COUNTIFS($C$2:C2, C2, $D$2:D2, D2) - COUNTIF($C$2:C2&C$2:C2, C2&D2)), "")...

Ramzi

Smartsheet uses "+" (Plus sign) for concatenation and not "&" like Sheets and Excel do

Replace & with + and you should be good to go.

Ramzi

Smartsheet uses "+" (Plus sign) for concatenation and not "&" like Sheets and Excel do

Replace & with + and you should be good to go.

s.anders

Hi Ramzi, there are + signs in my formula. This part above is one section of the concatenate. The whole formula as I currently have written is:

==IF(COUNTIFS(($[Supervisor forename]$1:[Supervisor forename]@row),[Supervisor forename]@row,($[Supervisor surname]$1:[Supervisor surname]@row),[Supervisor surname]@row)>1,CHAR(96)+COUNTIFS(($[Supervisor forename]$1:[Supervisor forename]@row),[Supervisor forename]@row,($[Supervisor surname]$1:[Supervisor surname]@row),[Supervisor surname]@row)-COUNTIF(($[Supervisor forename]$1:[Supervisor forename]@row)&([Supervisor forename]$1:[Supervisor forename]@row),([Supervisor forename]@row&[Supervisor surname]@row)),””)+

UPPER(LEFT([Supervisor forename]@row, 2)) + UPPER(LEFT([Supervisor surname]@row, 2)) + IF([Student forename]@row = "", "XX", UPPER(LEFT([Student forename]@row, 2))) + IF([Student surname]@row = "", "XX", UPPER(LEFT([Student surname]@row, 2))) + IF(OR([start date]@row = "Unknown", [start date]@row = "On hold", [start date]@row = "Unsuccessful", [start date]@row = "Withdrawn"), "00", RIGHT(100 + MONTH([start date]@row), 2)) + "." + IF(OR([start date]@row = "Unknown", [start date]@row = "On hold", [start date]@row = "Unsuccessful", [PhD start date]@row = "Withdrawn"), "00", RIGHT(YEAR([start date]@row), 2))

Paul Newcome

You do have a lot more parenthesis than you really need. We can work on cleaning them up if the next couple of adjustments still don't do the trick.

1: You need to remove one of the = from the beginning.

2: @Ramzi is correct about concatenation. I understand that you have + elsewhere, but you are still using the & even in the one portion to concatenate (see bold) cell data.

==IF(COUNTIFS(($[Supervisor forename]$1:[Supervisor forename]@row),[Supervisor forename]@row,($[Supervisor surname]$1:[Supervisor surname]@row),[Supervisor surname]@row)>1,CHAR(96)+COUNTIFS(($[Supervisor forename]$1:[Supervisor forename]@row),[Supervisor forename]@row,($[Supervisor surname]$1:[Supervisor surname]@row),[Supervisor surname]@row)-COUNTIF(($[Supervisor forename]$1:[Supervisor forename]@row)&([Supervisor forename]$1:[Supervisor forename]@row),([Supervisor forename]@row&[Supervisor surname]@row)),””)+UPPER(LEFT([Supervisor forename]@row, 2)) + UPPER(LEFT([Supervisor surname]@row, 2)) + IF([Student forename]@row = "", "XX", UPPER(LEFT([Student forename]@row, 2))) + IF([Student surname]@row = "", "XX", UPPER(LEFT([Student surname]@row, 2))) + IF(OR([start date]@row = "Unknown", [start date]@row = "On hold", [start date]@row = "Unsuccessful", [start date]@row = "Withdrawn"), "00", RIGHT(100 + MONTH([start date]@row), 2)) + "." + IF(OR([start date]@row = "Unknown", [start date]@row = "On hold", [start date]@row = "Unsuccessful", [PhD start date]@row = "Withdrawn"), "00", RIGHT(YEAR([start date]@row), 2))

s.anders

Hi Paul, thanks for your response.

Those '&' signs if you look closely are actually part of the function for IF, and not part of the concatenation. Apologies for the double =, it isn't actually in the formula I've used.

Paul Newcome

They may not be stringing two functions together, but they are still a concatenation of two cells. They need to be replaced with +. In Smartsheet, any time you are stringing two cells together, think of it as "adding" them.

[Column A]@row + [Column B]@row

s.anders

Ah! Ok, let me try that and come back

s.anders

Thank you! Amazing, it works

Paul Newcome

Happy to help. 👍️

Please don't forget to mark @Ramzi's initial response as the accepted answer so others that have the same challenge and come across this post can quickly see the solution.

s.anders

Wondering if you could help with next problem this produced -- it now has #invalidoperation on any of the identifiers that are the second project. Given that this is the purpose of the first part of the formula (to place a b, c,... etc as a prefix on any projects that are not the first of a given supervisor) it is necessary if I want to include the first part.

Paul Newcome

Are you able to provide some screenshots for context?

s.anders

I'm afraid not as the data is sensitive

Paul Newcome

@s.anders What about something mocked up with sample data?

s.anders

Linked - thanks for any help, the bottom one isn't working.

https://app.smartsheet.com/sheets/6XP6cHPRj7P6GFWGW3RJ3g6Ch5Xrp8HFCpqwhpF1

Paul Newcome

I don't have access to the sheet. Are you able to publish?

s.anders

Apologies - here you are: https://app.smartsheet.com/b/publish?EQBCT=d2efd9ce59854cc8be9b58b758d16cb3