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

  • Ramzi
    Ramzi ✭✭✭✭✭
    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

«1

Answers

  • Ramzi
    Ramzi ✭✭✭✭✭
    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

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ah! Ok, let me try that and come back

  • Thank you! Amazing, it works

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for context?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • I'm afraid not as the data is sensitive

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!