OK, now I know, where the error could be.
My column 'C' has only values till c40, rest all are blank.

So please advise what should I do in this case.

Thanks

"Peo Sjoblom" wrote:

It works trust me, so you have either #VALUE! errors (probably in C4:C96)
somewhere within your data or you simply didn't copy and pasted the formula
as posted


--


Regards,


Peo Sjoblom



"ub" wrote in message
...
using this formula , I get # value error.

Thanks for all your help.


"Peo Sjoblom" wrote:

You should use the formula I posted

=SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,)))



or use custom filtering and then simply

=SUBTOTAL(2,$A$4:$A$96)


nothing else



--


Regards,


Peo Sjoblom


"ub" wrote in message
...
Hi
I am using the formula :
=SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),SUBTOTAL(2,A4:A96))

Please advise , what misytake I am doing.

Thanks

"Peo Sjoblom" wrote:

I don't see how that formula can return a value error unless you
calculate
it with something else
like

=SUBTOTAL(2,A4:A96)*x

and what you calculate it with is a text value


also make sure you don't have any errors in your ranges although count
does
not get affected by errors like a sum would


--


Regards,


Peo Sjoblom



"ub" wrote in message
...
Hi, when I use Subtotal(2,a4:a96), It get error messgae # value.

Please advise

"Peo Sjoblom" wrote:

If you are going to use a filter then you don't need a formula like
this,
filter on blanks in M and P in C then just count the visible cells
in
A

=SUBTOTAL(2,A4:A96)

If you still want a formula and all your data starts in row 4 with
headers
in 3 you can use

=SUMPRODUCT(--(ISBLANK($M$4:$M$96)),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,)))


although I would probably change remove isblank and use range=""


=SUMPRODUCT(--($M$4:$M$96=""),--(TRIM($C$4:$C$96)="P"),--(ISNUMBER($A$4:$A$96)),SUBTOTAL(3,OFFSET($A$4,ROW( $A$4:$A$96)-MIN(ROW($A$4:$A$96)),,)))

This formula can be entered normally as opposed to your COUNT
formula


--


Regards,


Peo Sjoblom


"ub" wrote in message
...
Hi
I have the following formula in one of the cell
=COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$9 6),$A$4:$A$96))

But I have autofilter command and I want the rows to be counted
based
on
the
output of the filter results. But this formula always gives the
output
os
the
rows.

Please help