Don’t use * as a wildcard

[markh9999]

I have a countif statement that I use to find all exam grades that are A*. I store A* in a cell and use that cell in the countif. Problem is it picks up everything starting with A as it’s treating the * as a wildcard. Is there a way round this?

 

[Fluff]

You need to us this as the criteria A~*

 

[markh9999]

0You need to us this as the criteria A~*8

Ah yes - thank you but I'm not sure how to use that in what I want. I want the countif to look like #countif(a1:a10, b1) where b1 contains A* and all the cells A1 to A10 may contain an A*, A, B, C, D or E. So where would I put the ~ or do I have to put A~* in cell b1 ( I really wanted it to read A* and not A~*).

 

[Fluff]

You would need to put A~* in cell B1

 

[Fluff]

Another option would be to use sumproduct

=SUMPRODUCT(--(A1:A10=B1))

 

[RoryA]

You could also use:

=countif(a1:a10, SUBSTITUTE(b1,"*","~*"))

 

[markh9999]

You could also use:

=countif(a1:a10, SUBSTITUTE(b1,"*","~*"))

This is perfect - thank you very much. Thanks to everyone for their help.

 

[Fluff]

Glad we could help & thanks for the feedback.

In future when marking a solution please select the post that helped the most, rather than your reply. Thanks
I have changed it for you this time.