Nested if & countifs to generate a prefix based on number of occurrences
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)), "")...
Best Answer
-
Smartsheet uses "+" (Plus sign) for concatenation and not "&" like Sheets and Excel do
Replace & with + and you should be good to go.
Smartsheet Solutions Architect
www.adapture.com
Answers
-
Smartsheet uses "+" (Plus sign) for concatenation and not "&" like Sheets and Excel do
Replace & with + and you should be good to go.
Smartsheet Solutions Architect
www.adapture.com
-
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))
-
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)) -
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.
-
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
-
Ah! Ok, let me try that and come back
-
Thank you! Amazing, it works
-
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.
-
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.
-
Are you able to provide some screenshots for context?
-
I'm afraid not as the data is sensitive
-
@s.anders What about something mocked up with sample data?
-
Linked - thanks for any help, the bottom one isn't working.
https://app.smartsheet.com/sheets/6XP6cHPRj7P6GFWGW3RJ3g6Ch5Xrp8HFCpqwhpF1
-
I don't have access to the sheet. Are you able to publish?
-
Apologies - here you are: https://app.smartsheet.com/b/publish?EQBCT=d2efd9ce59854cc8be9b58b758d16cb3
Help Article Resources
Categories
Check out the Formula Handbook template!
