Countif array function not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ub - 26 Sep 2007 14:22 GMT

Hi
I am using this formula to count
=COUNT(IF((TRIM($C$4:$C$100)="C")*(TRIM($M$4:$M$100)<>"Closed"),$A$4:$A$100))
I am not getting the right results, because column $m$4:$m$100 has blank
cells , so this formula is counting the blank cells.
Please advise how can I correct this formula that is considers blank cells
in coulmn M.

Thanks

Reply to this Message

JE McGimpsey - 26 Sep 2007 15:21 GMT

One way (no need to array-enter):

=SUMPRODUCT(--ISNUMBER($A$4:$A$100), --(TRIM($C$4:$C$100)="C"),
--(TRIM($M$4:$M$100)<>"Closed"), --(LEN(TRIM($M$4:$M$100))>0))

> Hi
> I am using this formula to count
[quoted text clipped - 5 lines]
>
> Thanks

Reply to this Message

ub - 26 Sep 2007 15:32 GMT

Hi
It didn't work, it gives error message # Value

> One way (no need to array-enter):
>
[quoted text clipped - 10 lines]
> >
> > Thanks

Reply to this Message

JE McGimpsey - 26 Sep 2007 15:39 GMT

It works in a test workbook for me.

Do you have a #VALUE! error in your data range?

Did you use the formula exactly? If not, did you make sure the range
sizes were the same?

> It didn't work, it gives error message # Value
>
> > One way (no need to array-enter):
> >
> > =SUMPRODUCT(--ISNUMBER($A$4:$A$100), --(TRIM($C$4:$C$100)="C"),
> > --(TRIM($M$4:$M$100)<>"Closed"), --(LEN(TRIM($M$4:$M$100))>0))

Reply to this Message

Pranav Vaidya - 26 Sep 2007 15:22 GMT

change your formula as

=COUNT(IF((TRIM($C$4:$C$100)="C")*(TRIM($M$4:$M$100)<>"Closed"),$A$4:$A$100,0))

You were missing the last part of the IF condition, which I added as 0
(zero) in the above formula.

HTH,

Signature

Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!

> Hi
> I am using this formula to count
[quoted text clipped - 5 lines]
>
> Thanks

Reply to this Message

ub - 26 Sep 2007 15:34 GMT

Hi
It didn't work, it gives the same result ( incorrect numbers)

> change your formula as
>
[quoted text clipped - 14 lines]
> >
> > Thanks

Reply to this Message