Formula Help Needed
 

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!


--
Magic Speller
------------------------------------------------------------------------
Magic Speller's Profile: http://www.excelforum.com/member.php...o&userid=36830
View this thread: http://www.excelforum.com/showthread...hreadid=565406

Formula Help Needed
 

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


with @tiscali.co.uk


"Magic Speller"
wrote in message
news:Magic.Speller.2bkqx6_1153953909.4467@excelfor um-nospam.com...

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!


--
Magic Speller
------------------------------------------------------------------------
Magic Speller's Profile:
http://www.excelforum.com/member.php...o&userid=36830
View this thread: http://www.excelforum.com/showthread...hreadid=565406

Formula Help Needed
 

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"
wrote in message
news:Magic.Speller.2bkqx6_1153953909.4467@excelfor um-nospam.com...

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!


--
Magic Speller
------------------------------------------------------------------------
Magic Speller's Profile:
http://www.excelforum.com/member.php...o&userid=36830
View this thread: http://www.excelforum.com/showthread...hreadid=565406

Formula Help Needed
 

On Thu, 27 Jul 2006 00:06:24 +0100, "Sandy Mann"
wrote:

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
--
Keith

Formula Help Needed
 

On Thu, 27 Jul 2006 00:09:50 +0100, "Bob Phillips"
wrote:

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.)

--
Keith
--
Keith