Thanks so much JMB. both of them work fantastically!

Thanks Again

"JMB" wrote:

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" wrote:

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" wrote:

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".





"Jo" wrote:

Hi Biff

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

"Biff" wrote:

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

Biff

"Jo" wrote in message
...
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?

"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.