CountIf & null values problem

[chris100]

Hello all,

Small prolem with Countif. I have a function that counts if a cell is not blank - i.e has text in it:

=countif(v2:v200,"bob")

For some reason even if text is there it doesn't get counted.
Now the formula for cells where "bob" might appear is below:

=IF(D5>0,(IF(ISBLANK(L5)=TRUE,"bob","")),"")

This formula works fine but i have a suspicion it affects the countif.

Any suggestions??

Thanks,

Chris

[chris100]

Actually change the above question so that i count if a cell is non null - something like countif istext?

[Bob Phillips]

I cannot reproduce your problem as you state it.

Are you sure it is really "bob" in the cells, no leading or trailing spaces
anywhere. Try this and see what you get

=SUMPRODUCT(--(TRIM(V2:V200)="bob"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"chris100" <[email protected]> wrote in
message news:[email protected]...
>
> Hello all,
>
> Small prolem with Countif. I have a function that counts if a cell is
> not blank - i.e has text in it:
>
> =countif(v2:v200,"bob")
>
> For some reason even if text is there it doesn't get counted.
> Now the formula for cells where "bob" might appear is below:
>
> =IF(D5>0,(IF(ISBLANK(L5)=TRUE,"bob","")),"")
>
> This formula works fine but i have a suspicion it affects the countif.
>
>
> Any suggestions??
>
> Thanks,
>
> Chris
>
>
> --
> chris100
> ------------------------------------------------------------------------
> chris100's Profile:
http://www.excelforum.com/member.php...o&userid=25166
> View this thread: http://www.excelforum.com/showthread...hreadid=490689
>

[chris100]

Hi bob,

Thanks for the reply. I just had a bit of a thickie moment - i hadn't come back to the program for a few days and was working on the wrong cell that was referenced in the macro. I did check the macro but with my eyesight z and x looked very similar on the worksheet at 50%. oops.

Thanks all the same - with regards to the question i used wildcards to find anything missing*

=COUNTIF(V2:W268,"missing*")

To anyone unfamilar this counts all cells within the range that have "missing" written in the text. e.g missing stock, missing firm name, missing etc etc etc.

Cheers, chris