subtotal only unique in filtered list

[Guest]

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.

 

[Guest]

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(SUBTOTAL(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!)

 

[Guest]

Hi JMB

I actually want to filter Column B to In and then only count the unique
numbers in Column 1. Expecting to see 2 as answer. I put this formula in
and it returns #N/A?

 

[Biff]

Use JMB's formula but change all the references to column A.

Biff

 

[Guest]

Hi Biff

I tried to do that but it is still returning #N/A?

 

[Guest]

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

 

[Guest]

Assuming Co is Column A, Pos No is Column B, and Count is Column C, try

=SUM(--(FREQUENCY(IF((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1)))*($C$2:$C$8="in"),$B$2:$B$8,""),IF((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$8)-ROW($B$1),,1)))*($C$2:$C$8="in"),$B$2:$B$8,""))>0))

array entered w/Cntrl+Shift+Enter. Adjust the ranges as needed. Should
give you a count of unique Pos No entries that are visible and "in".

 

[Guest]

Hi JMB

It works! thank you so much for helping me...this is the first time I posted
a question even though I use the page all the time to help me...so thanks
very much.

 

[Guest]

you are welcome - thanks for the feedback

Hi JMB

It works! thank you so much for helping me...this is the first time I posted
a question even though I use the page all the time to help me...so thanks
very much.

 

[Jo]

Hi JMB

A while ago you helped me with this formula...I wondered could you help me
again please as I now have to add more info to it.

Columns are same
A B C
co Pos Count
..5 121 in
..7 123 in
..9 124 in
..9 124 in
..2 125 out

I basically need to do the same as before (count of unique Pos No entries
that are visible and "in") = 3

But i also now need to multiple it by the Co as well

Therefore it will now equal 2.1

Are you able to help me with this please? Thanks in advance.

 

[JMB]

try this formula - adjust ranges as needed:

=SUMPRODUCT(--(MATCH(B2:B8&"",B2:B8&"",0)=ROW(B2:B8)-ROW(B2)+1),SUBTOTAL(3,OFFSET(B1,ROW(B2:B8)-ROW(B1),,1)),--(C2:C8="in"),A2:A8)

gives me 2.1.

The original formula I gave you could be made a little shorter:
=SUMPRODUCT(--(MATCH(B2:B8&"",B2:B8&"",0)=ROW(B2:B8)-ROW(B2)+1),SUBTOTAL(3,OFFSET(B1,ROW(B2:B8)-ROW(B1),,1)),--(C2:C8="in"))

gives me 3.


Both formulas can be entered normally - no need for Cntrl+Shift+Enter like
the last one.

 

[Jo]

Thanks so much JMB. both of them work fantastically!

Thanks Again

 

[JMB]

quite welcome - thanks for posting back to let us know it is working okay.