Results 1 to 5 of 5

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

Threaded View

  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).

    B12=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(OFFSET(B1,1,0):B11,ROW(OFFSET(B1,1,0):B11)-ROW(OFFSET(B1,1,0)),,1)), IF(OFFSET(B1,1,0):B11>"",MATCH("~"&OFFSET(B1,1,0):B11,OFFSET(B1,1,0):B11&"",0))),ROW(OFFSET(B1,1,0):B11)-ROW(OFFSET(B1,1,0))+1),1))
    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

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