# Count Unique Filtred Values In A List: Do Not Count If Meets Criteria

#### chloec

##### Member
Hello - I would like to Count Unique Filtered Values In A List but Subtract Or Do Not Count if meets criteria

Here's the current working formula: =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(F9,ROW(F9:F999)-ROW(F9),,1)), IF(F9:F999>"",MATCH("~"&F9:F999,F9:F999&"",0))),ROW(F9:F999)-ROW(F9)+1),1))

It Count Unique Filtered Values In A List

Steps:
1. Count Unique Filtered Values In A List
2. Subtract all Counts Of Unique Filtred Values In A Listthat contain USER 8 (for example)

Thank you!

#### Attachments

• Screenshot 2023-08-15 at 1.25.08 PM.png
324.1 KB · Views: 11
Last edited:

#### chloec​

Why do You shouting ( use capital letters ) ?

Hi Vietm, I did not intend to shout and have fixed my posting thank you

Hi Vietm, I did not intend to shout and have fixed my posting thank you
Hi Vletm, spelled your name wrong, sorry

A sample sheet would be more helpful than a picture...

Here is the sample sheet, attached. Thank you @pecoflyer

#### Attachments

• Example_Chandoo.xlsx
10.3 KB · Views: 8
Thx. And which XL version are you using?

Thx. And which XL version are you using?
MS excel for Mac - Microsoft 365 subscription version 16.75.2. @pecoflyer thank you!

Not rally knowing where the filtered range comes from
Something like
Code:
``=COUNTA(FILTER(UNIQUE(F9:F28),NOT((ISNUMBER(SEARCH("user 8*",UNIQUE(F9:F28)))))*(UNIQUE(F9:F28)<>"")))``

This didn't work for me
Not rally knowing where the filtered range comes from
Something like
Code:
``=COUNTA(FILTER(UNIQUE(F9:F28),NOT((ISNUMBER(SEARCH("user 8*",UNIQUE(F9:F28)))))*(UNIQUE(F9:F28)<>"")))``
Thank you this didn't work for me, it seemed to return a count that included items in the filtered list. I also wanted to count the unique values or iterations that start with User 8 So I could subtract them. Thank you for trying, @pecoflyer

Maybe I can use a helper field instead. Could you suggest a solution for below?

1. Given a filtered list
2. If A certain text appears in my filtered list, count the occurrence once
Text A = 1 (doesn't matter how many times it displays in the filtered list, if it shows up once, give me a 1)
Text B = 1 (doesn't matter how many times it displays in the filtered list, if it shows up once, give me a 1)
Text C = 1 (doesn't matter how many times it displays in the filtered list, if it shows up once, give me a 1)
3. I will add up all the times my text A, B, C appears and subtract it from the count of ALL unique values (in cell A1 of my workbook)

What do you mean by " filtered list"?

A list where I have filtered out data and only want to count what is being displayed. The filtered list starts in cell F8

I'd use a helper column with SUBTOTAL formulas to identify the visible rows, then you can include that in the FILTER function. See attached demonstration.

#### Attachments

• Example_Chandoo.xlsx
10.5 KB · Views: 6
A list where I have filtered out data and only want to count what is being displayed. The filtered list starts in cell F8
So what's wrong with my suggestion?

Thank you so much!
@pecoflyer When I tried your suggestion, it didn't work out as expected - but that may have been user error.
@Debaser - I was trying to avoid a helper column, but if I have to use one - I will

I will continue to test your two solutions and report back.

Thank you very much excel superstars. You are so appreciated and generous with your time <insert heart shape>
I have been using Chandoo for many years as I continue to grow my skills - and I am always awed at the helpful expertise on display in the forums.

When I tried your suggestion, it didn't work out as expected - but that may have been user error.
Strange

#### Attachments

• Example_Chandoo(2).xlsx
10.7 KB · Views: 8
I'd use a helper column with SUBTOTAL formulas to identify the visible rows, then you can include that in the FILTER function. See attached demonstration.
Without helper, try this:

=SUM(0+ISTEXT((UNIQUE(IF(SUBTOTAL(103,OFFSET(F9,ROW(F9:F28)-ROW(F9),0,1))*ISERR(SEARCH("user 8",F9:F28))=1,F9:F28)))))

Last edited:
The only thing that distinguishes this solution from others is that I always use defined names and multi-line formulas.
Code:
``````= LET(
filteredList,  FILTER(list, ISTEXT(list) * ISERROR(FIND("User 8", list))),
distinctItems, UNIQUE(filteredList),
countDistinct, ROWS(distinctItems),
countDistinct
)``````
The objective is to avoid brevity and instead indicate the intent of every step of the solution process.

I just want to say thank you to all contributors! Appreciate all your help, support and technical skills!
My problem is RESOLVED. @pecoflyer @Debaser @Peter Bartholomew @bosco_yip

I ended up using the formula presented by @bosco_yip because I had challenges (user error) with @pecoflyer (the formula counts all row data, not the filtered data, though I see how you accounted for it, not sure why it didn't work with my data). I appreciate everything!

Most welcome