Hi everyone, I have a table with a lot of duplicated IDs, want to count unique ID when filter is on. I came across a formula online then copy it to my spreadsheet, but it is not working. How does array formula work?
=sum(--(frequency(if(subtotal(3,offset(Table1[ID],match(row(Table1[ID]),row(Table1[ID])-1,0,1)),countif(Table1[ID],"<"&Table1[ID]),""),countif(Table1[ID],"<"&Table1[ID]))>0))
many thanks!
Hi Tina
Can you please upload an example workbook so that we dont have to spend time recreating the data.
https://www.myonlinetraininghub.com/excel-forum/forum-rules-and-guides/read-this-first
sorry that forgot about it. just attached the file.
wanted to count unique ID if filtering different fund. thanks
I think this works:
=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Table1[ID],ROW(Table1[ID])-MIN(ROW(Table1[ID])),0,1)),COUNTIF(Table1[ID],"<"&Table1[ID]),""),COUNTIF(Table1[ID],"<"&Table1[ID]))>0))
It needs to be array entered using Ctrl+Shift+Enter unless you're on O365.
Hi Tina,
An alternative, you could use the new LET function (if you have it)
=LET(Unique_ID,UNIQUE(SUBTOTAL(105,OFFSET(F2,ROW(Table1[ID])-MIN(ROW(Table1[ID])),0))),
Filtered_ID,FILTER(Unique_ID,Unique_ID>0),
Filtered_Count,COUNT(Filtered_ID),
Res,Filtered_Count,
Res)
If you don't have LET you can put this into a cell, say H1
=UNIQUE(SUBTOTAL(105,OFFSET(F2,ROW(Table1[ID])-MIN(ROW(Table1[ID])),0)))
and then in I1
=COUNTIF(H1#,">0")
COUNTIF requires a range so you can't wrap it around UNIQUE which returns an array.
You can read about the 'old style' array formulae here and the new dynamic arrays
Regards
Phil
Thanks Velouria. I am not using O365, and I keep getting message 'You've entered too many arguments for this function.' - I am frustrated now...
Thanks Phil, I downloaded your file, but the formula results show as '#NAME?', I don't know why.
I tried inputting the formula as you suggested, and got the same as above. is it any functionality of my excel not on? Really appreciated with your help!
Hi Tina,
My solution requires dynamic arrays which you won't have if you don't have O365.
Velouria's formula works for me - see H1 in attached file - but it must be entered by using CTRL+SHIFT+ENTER as it is an array formula, rather than a non-array formula you enter just by hitting the ENTER key.
Please read the links I provided which will explain about CSE (CTRL+SHIFT+ENTER) array formulae.
Regards
Phil
Thanks for your prompt reply! Does this formula work if the table has a query link behind? I still couldnt get Velouris's formula work on my spreadsheet.
Hi Tina,
It should work fine.
Did my formula work for you in the file I attached to my last post?
Maybe you need to attach your file where it's not working so we can have a look.
Phil
