Forum Discussion

GregorySD's avatar
GregorySD
Copper Contributor
Oct 19, 2023
Solved

Unique Count of Filtered Excel Workbook

Hi   I hope that someone out there can assist me. I have a commercial invoice in excel for some of our clients, which is filterable by area. The problem I am sitting with is that I need to give t...
excel
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Oct 19, 2023

    GregorySD 

    You can use this complicated formula:

     

    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A385)-ROW(A9),,1)), IF(A9:A385>"",MATCH("~"&A9:A385,A9:A385&"",0))),ROW(A9:A385)-ROW(A9)+1),1))

     

    A9:A385 is the range in which you want to count unique values, and A9 is the first cell in this range.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 19, 2023

GregorySD As a variant, add an extra column that 'calculates' if the row is visible or not. I've done that in column I. Then this formula in F5 will achieve what you ask for:

 

 

=COUNTA(UNIQUE(FILTER(A9:A384,I9:I384=1)))

 

 

See attached.

 

As a general recommendation, it's better to use a structured table. That will eliminate the need for direct cell references which presumable may vary every time. Rather than referencing from row 9 to row 384 you can reference a column name which is part of the table without the need to specify how many rows it should look for. Just a tip.

Resources