countif for text "*A"

[Diddy]

Hi,

Can anyone help with this one please
=countif(C3:C41,"*A") searching for number of *A but it returns all "A" as well.

Cheers
Diddy

[edvwvw]

Try this

=SUMPRODUCT(--(C3:C41="*A"))

edvwvw

[TMS]

Try:

=COUNTIF(C3:C41,"~*A")

Regards

[teylyn]

the asterisk * is a wildcard. To search for the text string that contains an asterisk, you have to precede it with a tilde ~ like this

=countif(C3:C41,"~*A")

Edit: The asterisk is a wildcard for any number of characters. The ? sign is a wildcard for a single character. If you want to use any of these in a countif value, you need to escape them with the tilde sign ~. If your search string contains an actual tilde sign, you will need to double it

=countif(C3:C41,"~?A") will count ?A
=countif(C3:C41,"~~A") will count ~A

cheers

[edvwvw]

Thanks Tetlyn - wasn't aware that you could use a tilde to search for *

edvwvw

[Diddy]

Try this

=SUMPRODUCT(--(C3:C41="*A"))

edvwvw

Hi,

Works a treat but how?

after reading the other posts just wondered why sumproduct does not recognise * as wildcard?

Cheers
Diddy

[teylyn]

Diddy, as far as I know, Sumproduct does not support wildcards in its comparison strings. It's verbatim as it is.

Countif(), Sumif() or the newer versions Countifs() and Sumifs() in Excel 2007 and later are preferrable over Sumproduct, though, because they calculate a lot faster.

[DonkeyOte]

Following on from Teylyn's post ...

Functions supporting Wildcards include (in addition to those listed already)

MATCH & VLOOKUP (if using FALSE/0 return_type)
SEARCH

Others like FIND, SUBSTITUTE, EXACT etc do not support wildcards.

Reiterating Teylyn's point regards SUMPRODUCT - wildcard support only viable if you were using one of the aforementioned functions in embedded form, eg:

=SUMPRODUCT(--ISNUMBER(SEARCH("~*",C3:C41)))

not that you would use it...