Very weird problem, need help.

[bomberman411]

Hello all,

I have a weird problem that I can't figure out... might be a fluke in Excel, I just don't know.

In my spreadsheet, I have 3 different/separate cells to calculate totals that I need. All 3 checks the same long column for different things:

- one to check for zeros ("0") using

=COUNTIF($F$11:$F$5000;0)

- one to check for question marks ("?) using

=COUNTIF($F$11:$F$5000;"?")

- one to check for dashes ("-) using

=COUNTIF($F$11:$F$5000;"-")

All of these use to work fine, but recently I have noticed something. When I add a "?", the total is incremented like it should. BUT if I write a "-", I have BOTH totals (total for the question marks & total for dashes) increment at the same time...? I really don't know what could be wrong.

Please help.

 

[Andrew Poulsom]

The question mark is a wildcard with COUNTIF. To count the actual character you have to precede it with a tilde:

=COUNTIF($F$11:$F$5000,"~?")

 

[xld]

? is a wildcard, =COUNTIF($F$11:$F$5000;"?") is counting all cells with a single character in them. Try =COUNTIF($F$11:$F$5000;"~?")

 

[bomberman411]

Thank you very much guys...

I knew it had to be something like that, I suspected as much.
Thanks for letting me know.