IF & COUNTIFS formula when dealing with names

Hi! I deleted rows which contained formulas and saved (i know i shouldn't have) and I tried getting it back, but can't figure out what the formula should be. It keeps saying #unparseable.

If a name is unique, they were given a number and if the name was repeated, it was supposed to be blank. This is how it should look:

When Megan repeats, NameID column should be blank.


Instead, it's showing this:


It looks like the formula was

=IF(COUNTIFS(#REF:Name@row, Name@row) = 1, COUNTIFS(#REF:Name@row, OR(ISBLANK(@cell), NOT(ISBLANK((@cell))))))

I tried removing where the #REF is in the formula, and it shows #1 for everyone, where it should be Megan 1, William 2, Armine 3, etc.

I'm hoping someone can help me fix my mess.

Answers

  • Austin Smith
    Austin Smith ✭✭✭✭✭

    @MeganAcuzar

    Try this:

    =IF(COUNTIF([Name]:[Name], [Name]@row) = 1, COUNTIFS([Name]$1:[Name]@row, OR(ISBLANK(@cell), NOT(ISBLANK((@cell))))

  • @Austin Smith

    so it did and didn't work. The Name ID column gave me a blank when the name repeated - which is exactly what i needed - but the index field stays at #1. I probably have that formula wrong as well:

    =COUNTIF(NameID@row, <>"")

  • Austin Smith
    Austin Smith ✭✭✭✭✭

    @MeganAcuzar

    What's the point of the index column if it just repeats the Name ID? What did you want it to do?

  • @Austin Smith

    This sheet was set up before me so I'm trying to get it back in working order.

    Essentially, we keep track of a users access. The form I am having issues with documents every single request we have received (Request Sheet). This data then gets moved to another sheet (Record File) which condenses information per user.

    For example:

    I request 2 different programs on 2 different days, so it shows as 2 separate requests on the Request Sheet. In the Record File sheet, I want one entry for my name, but it shows both programs that I requested.

  • Austin Smith
    Austin Smith ✭✭✭✭✭

    @MeganAcuzar

    Can you submit a screenshot of the source sheet that you want to pull data into the index field from?

    =COUNTIF(NameID@row, <>"") is definitely the wrong formula, but I may need a bit more context.

    Most likely it will just be something along the lines of =countif({record file name ID column}, [name id]@row) if you're just looking for the number of times their name shows up on the record file sheet.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!