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.

Find more posts tagged with

Accepted answers

All comments

Austin Smith

@MeganAcuzar

Try this:

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

MeganAcuzar

@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

@MeganAcuzar

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

MeganAcuzar

@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

@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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.