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.