Counting unique values with 2 criteria

AFlint

I am looking for a formula that will help me summarize the count of unique values on another sheet.

Criteria 1: Office abbreviation (ex: ABC)

Criteria 2: Unique name

I am trying to get a count of Criteria 2 for each office (Criteria 1)

I tried

=COUNTIFS((DISTINCT{Criteria 2})),<>"",{Criteria 1},"ABC")

Return: #INCORRECT ARGUMENT SET

I tested the first half- =COUNTIFS((DISTINCT{Criteria 2})),<>"") and it returned the correct count for the single criteria

I tested the second half- =COUNTIFS({Criteria 1},"ABC") and also received the correct count for the single criteria

Is there something else I can do to combine these to get only the ones that meet both criteria?

Find more posts tagged with

Accepted answers

Bassam Khalil

@AFlint

Please try the following formula:

=IFERROR(COUNT(DISTINCT(COLLECT([Unique Names]:[Unique Names], Office:Office, 
@cell = "Office 1"))), "")

the following screenshot shows the result:

All comments

Bassam Khalil

Hi @AFlint

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

My Email for sharing : Bassam.k@mobilproject.it

AFlint

Hi @Bassam Khalil

Attached is a screenshot of a simplified version of the sheet.

I'm trying to get a count of unique values in "Unique Names" when they are in Office 1. The attached would return 2, since "Unique 1" appears twice. The way I have my sheet set up, the names only appear if the box is checked urgent (the underlying formula is First Name + Last Name). For this reason, I only get a count of the Urgent rows because they are the only rows that have names in them.

Thank you in advance for your help.

Bassam Khalil

@AFlint

Please try the following formula:

=IFERROR(COUNT(DISTINCT(COLLECT([Unique Names]:[Unique Names], Office:Office, 
@cell = "Office 1"))), "")

the following screenshot shows the result:

AFlint

Thank you! Now, one follow up question- would this allow me to add additional factors? For example, if I wanted to add a third criteria for status or similar? If so, where in the string would I add it?

Bassam Khalil

@AFlint

Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Or if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

AFlint

Sorry, I don't actually have an example- I'm just asking out of curiosity in case the situation ever arises.

Bassam Khalil

@AFlint

The following formula show if we need to add Status for example ( Completed )

=IFERROR(COUNT(DISTINCT(COLLECT([Unique Names]:[Unique Names], Office:Office,
@cell = "Office 1", Status:Status, @cell = "Completed"))), "")

the following screenshot shows the result

AFlint

Thank you!

Bassam Khalil

@AFlint

You are welcome

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.