Counting a Unique identifier in a column

Hello SS Community,

I am halfway there but seem to be doing something wrong- I want to count how many times a Person shows up in the list and record the number.

I have this formula =IF(COUNTIF([Person Number]:[Person Number], [Person Number]@row) > 1, 1) and it is working perfectly to record when the person number in the Person number column shows up more than once but when I add the second layer to record two when the person number shows more than two =IF(COUNTIF([Person Number]:[Person Number], [Person Number]@row) > 1, 1) IF(COUNTIF([Person Number]:[Person Number], [Person Number]@row) > 2, 2)) It does not do anything and still records 1 even when the exact person number is in the column twice.

I also tried to use this formula =COUNTIF([Person Number]:[Person Number], [Person Number]@row) which worked but the reason I can't use this is I don't want to count the first occurrence of the person number in the sheet- I am trying to use it to count how many times the person number was rescheduled, so the first occurrence should be removed from the count.

Find more posts tagged with

Accepted answers

Heather Duff

@Omotola Ashafa ,

Try this, then:

=if(COUNTIF([Person Number]:[Person Number], [Person Number]@row)>1,COUNTIF([Person Number]:[Person Number], [Person Number]@row)-1,"")

Really all you're doing is subtracting 1 from the count if it qualifies, but it should do the trick!

All comments

Heather Duff

Hi @Omotola Ashafa,

Try this:

=if(COUNTIF([Person Number]:[Person Number], [Person Number]@row)>1,COUNTIF([Person Number]:[Person Number], [Person Number]@row),"")

This should only list the number of occurrences if there's more than one row with that person; otherwise, it will be blank.

Let me know if it works!

Best,

Heather

Omotola Ashafa

Thank You @Heather D ,

It works as you explained. The only thing is we would like the total rescheduled number only, so we want the first occurrence removed from the count.

Heather Duff

@Omotola Ashafa ,

Try this, then:

=if(COUNTIF([Person Number]:[Person Number], [Person Number]@row)>1,COUNTIF([Person Number]:[Person Number], [Person Number]@row)-1,"")

Really all you're doing is subtracting 1 from the count if it qualifies, but it should do the trick!

Omotola Ashafa

@Heather D

It worked, thank you so much and thanks for explaining the formula too

Heather Duff

@Omotola Ashafa You're welcome! I'm glad it worked.

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.