+ Reply to Thread
Results 1 to 5 of 5

COUNTIFS Formula for Filtered List to Find Unique Values (Ignore Hidden Rows)

  1. #1
    Registered User
    Join Date
    06-13-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    40

    COUNTIFS Formula for Filtered List to Find Unique Values (Ignore Hidden Rows)

    I have a filtered list in which there are multiple options in Column A, Column B, amounts in Column C and other filtered labels in Column D. I have figured out how to calculate subtotals ignoring the hidden rows using the AGGREGATE function but I'm also trying to output the unique COUNTs of items with duplicate listings in Column A/B. I already have a formula below to count the unique instances in column B below (4 unique names).

    Please Login or Register  to view this content.
    What I want is a formula in B13, B14, B15 to count the unique instances with the selected Column A's unique value i.e. say you filtered Column A to be only "Normal", then the unique count of B12 would be 2 because Audi and Mercedes are the only two unique values...but Audi also shows up in filtered column A "special" so B13 desired output should actually be =1. B14 should =2 (BMW and Honda are the only unique values with "Special" in Column B) and B15 should =1 since Audi shows up in both Normal and Special Column A.

    I've attached a sample sheet, i hope this makes sense / isn't too confusing for finding unique instances of text with filtered columns (obviously my actual data set is much wider and i plan to manipulate the filter table with other columns D-Z etc. unrelated to the column A/B that i want to find unique values of).
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    10-22-2022
    Location
    Santa Cruz, Bolivia
    MS-Off Ver
    MSO 365
    Posts
    157

    Re: COUNTIFS Formula for Filtered List to Find Unique Values (Ignore Hidden Rows)

    hi there, after check the consult I attach my proposal solution with an no array and array formulas:

    for B12:

    Please Login or Register  to view this content.
    For B13:

    Please Login or Register  to view this content.
    For B14:

    Please Login or Register  to view this content.
    For B15:

    Please Login or Register  to view this content.
    I hope it results useful.

    Count unique conditional values (Solution).xlsx

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: COUNTIFS Formula for Filtered List to Find Unique Values (Ignore Hidden Rows)

    I'm confused by your request!!

    You want the formula to IGNORE hidden rows.

    If you filter column A to show NORMAL, you want B13 to show 1. OK, that's
    =LET(A,A2:A10,B,B2:B10,C,BYROW(A,LAMBDA(x,AGGREGATE(3,5,x))),IFERROR(ROWS(UNIQUE(FILTER(B,(C=1)*(COUNTIFS(B,B,A,"<>Normal")=0)))),0))


    But then you say.. that you want Special to show 1... How can it, if you want to IGNORE hidden rows and you have already filtered the llist to show only Normal???

    ExceLogan's formula seems to match your text, but I've also incuded calculations for B12,B14 & B15 based on and alternative interpretation of your request. He's probably correct, but take a look at the pale purple cells in the attached.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  4. #4
    Registered User
    Join Date
    06-13-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    40

    Re: COUNTIFS Formula for Filtered List to Find Unique Values (Ignore Hidden Rows)

    @Glenn Kennedy - this is amazing and perfect, thank you so so much!!!

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: COUNTIFS Formula for Filtered List to Find Unique Values (Ignore Hidden Rows)

    You're welcome. I assume that my interptreation was correct??



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Get unique values with Aggregate Index Match Formula ignoring hidden rows and columns
    By dummy777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-06-2020, 02:22 AM
  2. Find last row including hidden and filtered rows
    By jpruffle in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 09-04-2019, 12:08 AM
  3. [SOLVED] Dynamically Extracting Unique Values from Filtered List (Visible Rows Only)
    By AliGW in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-08-2018, 10:37 AM
  4. [SOLVED] How do I ignore values while copying from hidden rows?
    By Mayank in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 AM
  5. [SOLVED] How do I ignore values while copying from hidden rows?
    By Mayank in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. [SOLVED] How do I ignore values while copying from hidden rows?
    By Mayank in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  7. Unique Count sensitive to hidden/filtered rows
    By Ian in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-12-2005, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1