Wildcard characters in formulas

[TARS1]

I am trying to understand why wildcard characters don't always work.
Consider the following table:

A
1 Yes
2 No
3 Cell with one space
4 Cell with two spaces
5 Cell with five spaces

Formula =COUNTIF(A1:A5, " ") counts cells containing with ONE spac
and = 1.
Formula =COUNTIF(A1:A5, " *") counts cells beginning with a space an
= 3.
The wildcard character "*" works!!!

Formula =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,{"Yes","No"," "},0)))
counts cells beginning with "Yes", "No" or one space and = 3.

In order to count all cells with "Yes", "No" or that begin with
space, I added the wildcard character to the formula below, expectin
it to return a value of 5. It came back with 2 as no cells begin wit
a space and an asterick.

Formula =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A5,{"Yes","No"," *"},0))))

Formula =SUM(IF((A1:A5="Yes")+(A1:A5="No")+(A1:A5=" *"),1,0)) also
2.


Why didn't the wildcard character work in either formula and return
value of 5?

Where can I get some additional information on this subject?

Also, is there a quick way to insert "$" into a formula to create
absolutes?

Thanks,

Ala

 

[swatsp0p]

According to the Help info:
-If match_type is 0 and lookup_value is text, lookup_value can contai
the wildcard characters asterisk (*) and question mark (?). An asteris
matches any sequence of characters; a question mark matches any singl
character. -

As you are looking at TRUE and FALSE, which are really 1 and 0, I a
guessing this precludes the use of wildcards in the MATCH fx.

To your question of quick changes for Absolute cell references, if yo
highlight your ref. in the formula bar and press F4 it will change A
to $A$1. Pressing F4 again will make it A$1, again it will be $A1 an
once again will revert it to A1.

HT