Count if function

[ub]

Hi
I have the following formula in one of the cell
=COUNT(IF((TRIM($C$4:$C$96)="P")*ISBLANK($M$4:$M$96),$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

 

[Peo Sjoblom]

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]

Hi, when I use Subtotal(2,a4:a96), It get error messgae # value.

Please advise

 

[Peo Sjoblom]

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]

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]

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]

using this formula , I get # value error.

Thanks for all your help.

 

[Peo Sjoblom]

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]

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