Help with Count If formula on unique values only

I am trying to add a count to my sheet summary which gives me the number of contracts which are a certain status within my sheet eg. pending, signed & returned etc but each contract can be listed a number of times within the sheet and I only want the contract to count once in my summary.

For Example:

These entries are all for the same contract, 597, but in my count it shows as 25 entries not just 1.

I have seen a number of threads on similar issues and tried a whole combination of COUNTIFS, ANDS, COLLECT, DISTINCT formula combinations but each one has returned an error. Can anyone help me with the correct functions / formula for this?

Thanks

Vic

Find more posts tagged with

Formulas

Accepted answers

All comments

Ramzi K

@VicR

The combination of functions you're looking for are probably this: =COUNT(DISTINCT(COLLECT(

In your example, this would get you a count of 1 instead of 25:

=COUNT(DISTINCT(COLLECT([Contract Status]:[Contract Status], [Contract Status]:[Contract Status], [Contract Status]@row, [Coupa Contract Number]:[Coupa Contract Number], [Coupa Contract Number]@row)))

I hope this helps.

Cheers,

Ramzi

VicR

@Ramzi K thank you!

how about if i were to use this in a sheet summary on the whole sheet? i have tried the formula as above but i get #unparseable any ideas on how it needs to be changed to work on the whole sheet with different contract numbers?

Ramzi K

@VicR

Can you copy/paste your formula so I can see it?

Cheers,

Ramzi

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.