Log In
Register
Home
June 1, 2018
Offline
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!
Trusted Members
December 20, 2019
Offline
Hi Tina
Can you please upload an example workbook so that we dont have to spend time recreating the data.
June 1, 2018
Offline
sorry that forgot about it. just attached the file.
wanted to count unique ID if filtering different fund. thanks
Trusted Members
Moderators
November 1, 2018
Online
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.
October 5, 2010
Offline
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
June 1, 2018
Offline
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...
June 1, 2018
Offline
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!
October 5, 2010
Offline
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
1 Guest(s)
