Forum Discussion
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 them a total boxes count per area but with the counta unique function, I can only get a total count, not filterable.
The Total Boxes count sits in column F5 and is counting the unique values in column A.
My client will filter it by column C (Property) and I need the box count to alter depending on the property selected.
I am attaching my workbook to ensure that you can see what I am trying to accomplish.
https://1drv.ms/x/s!AvrIqSOp1qzQb-tz2XjFufigcpw?e=ZsyTFA&nav=MTVfezMyMDdGNUNBLTY1NTUtNDk0NS1COEUxLUUwOTU0QjgzNzczMn0
Hopefully someone out there can assist.
Regards
Gregory
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.
4 Replies
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.
Clicking your link I get a blank page only... So attached is a sample:
Data formated as Table named Table1. Not mandatory, you can update the below OFFSET with your actual Range:
=COUNTA( UNIQUE( FILTER(Table1[Item],SUBTOTAL(3,OFFSET(Table1[Property],ROW(Table1)-ROW(Table1[#Headers])-1,,1))) ) )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.
- Thank you so much Hans, this works perfectly.
