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

[email protected]
[email protected] 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]...