# Counting unique values with 2 criteria

✭✭✭✭

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?

• ✭✭✭✭✭✭

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

the following screenshot shows the result:

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭

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

bassam.khalil2009@gmail.com

• ✭✭✭✭

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.

• ✭✭✭✭✭✭

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

the following screenshot shows the result:

bassam.khalil2009@gmail.com

• ✭✭✭✭

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?

• ✭✭✭✭✭✭

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

bassam.khalil2009@gmail.com

• ✭✭✭✭

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

• ✭✭✭✭✭✭

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

bassam.khalil2009@gmail.com

• ✭✭✭✭

Thank you!

• ✭✭✭✭✭✭

You are welcome

bassam.khalil2009@gmail.com