• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

C

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)

Please see image for example.

Thank you!
 

Attachments

  • Screenshot 2023-08-15 at 1.25.08 PM.png
    Screenshot 2023-08-15 at 1.25.08 PM.png
    324.1 KB · Views: 16
Last edited:
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
pecoflyer said:
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)<>"")))
Click to expand...
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)
 
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: 7
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.
 
Debaser said:
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.
Click to expand...
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!
 
You must log in or register to reply here.
Back
Top