Formula working for one data value but not another

Hi Community,

I have a sheet that contains, among others, columns titled "Priority" and "Contract ID". Multiple rows can have the same Contract ID and the same Priority. Priority values are 1-2-3 and restricted to dropdown list values only. To collect the number of unique Contract IDs that are Priority "1", I have this formula in the sheet summary:

=COUNT(DISTINCT(COLLECT([Contract ID]:[Contract ID], Priority:Priority, "1")))

That returns a correct count. Same thing for Priority "3":

=COUNT(DISTINCT(COLLECT([Contract ID]:[Contract ID], Priority:Priority, "3")))

But my formula for Priority "2", which I copy-pasted from these and only changed the value between the quotation marks, returns a count of "1":

=COUNT(DISTINCT(COLLECT([Contract ID]:[Contract ID], Priority:Priority, "2")))

I checked the data in the Priority column and the 2 values are just 2, no additional spaces or other characters. I deleted all the 2s and re-entered them, and still the formula comes back with the same count of "1".

I'm completely stumped - I even asked the AI to write a new formula to collect the data, same result. Can anyone help?

Find more posts tagged with

Sheets

Formulas

Project Management

Accepted answers

Paul Newcome

Can you provide a screenshot of the rows that are supposed to be getting counted but are not? I am particularly curious about the [Contract ID] column. Are they all numbers? Do some have letters and numbers, but others don't? Do some have leading zeros but others don't?

All comments

Heidi Truitt

I would try removing the "" n the formula ie

=COUNT(DISTINCT(COLLECT([Contract ID]:[Contract ID], Priority:Priority, 2)))

Liz W

Thank you for the suggestion, unfortunately it didn't change the result.

Paul Newcome

Liz W

Thank you Paul - I had checked the data in the Priority column, but not the Contract ID column - I found 7 lines (out of almost 7K!) that had text. Mystery solved!

Paul Newcome

Happy to help. 👍️

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.