

June 1, 2018

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

Hi Tina
Can you please upload an example workbook so that we dont have to spend time recreating the data.


Trusted Members
Moderators

November 1, 2018



October 5, 2010

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


October 5, 2010

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)
