Formula Help Needed

[Magic Speller]

Hi, everyone.

I have two questions:

1. Can anyone tell me what formula I can use to count the number of
rows with an "X" in column A and either an "X" or an "(X)" in column
B?

In other words, when applied to:

A B
1 X
2 X X
3 X (X)
4 X
5 X X

it should give me 3.

2. Is there a way to get the '*' character recognized as a character,
and not a wildcard, in a formula? I want to use something like:

COUNTA(A10:A200, "X*")

but if I do it this way, of course, it will count "X*", "XX", and
"X***".

Thanks for your help!

 

[Sandy Mann]

COUNTA() does not work that way - checkit out in Help. Try using COUNTIF()

=COUNTIF(A1:A9,"x")

=COUNTIF(B1:B9,"x")+COUNTIF(B1:B9,"(x)")

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


"Magic Speller" <[email protected]>
wrote in message
news:[email protected]...

 

[Bob Phillips]

1. =SUMPRODUCT((A1:A100="X")*(B1:B100={"X","(X)"}))

2. =COUNTIF(A:A,"X~*")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Magic Speller" <[email protected]>
wrote in message
news:[email protected]...

 

[Keith Russell]

COUNTA() does not work that way - checkit out in Help. Try using COUNTIF()

Oops! I meant to type COUNTIF, of course. Washed my fingers and
can't do a thing with them....

Thanks for the reply.

 

[Keith Russell]

1. =SUMPRODUCT((A1:A100="X")*(B1:B100={"X","(X)"}))

2. =COUNTIF(A:A,"X~*")

Thanks VERY much, Bob. Those were exactly what I needed:

1. The logical product makes sense. Interestingly, though, when I
looked up SUMPRODUCT in Excel Help and in several Excel books I
checked out of the library, none of them covered its use in this
way. So I very much appreciate your expertise.

2. So it appears that '~' is the escape character in Excel? (I
couldn't find this mentioned anywhere, either.)