Hi
I have got this formula to work and it will tell me the correct numbers when
I apply the filter. However, can I also get it to tell me for instance if I
don't apply any filters that there are 3 position nos (unique id) that equal
in (count column)?. I would also need the formula to work if I applied the
filter to Co and selected org because then I would have 2 positions that are
in. Hope this makes sense.

Co pos no count
org 101 in
org 119 in
adj 211 out
adk 210 out
org 101 in
one 301 in

"JMB" wrote:

When you filter column A for 1, you want to count the number of unique
visible cells in column B?

You could try:
=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8 )-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""),IF(SUB TOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1)),MATCH($B$2:$B$8,B2:B8,0),""))0))

array entered w/Cntrl+Shift+Enter (or you get #VALUE!)


"Jo" wrote:

I have a spreadsheet that has for example two columns (filters are on this
data)
a b
1 in
1 in
2 out
2 out
3 in
3 in
I have written the formula
=SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1))*($B$2:$B$8="IN")))
However, when I filter to 1 it says two but I want it to say 1 because I
want it to count unique only.

can someone please help me.