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

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

[Keith Russell]

On Thu, 27 Jul 2006 00:06:24 +0100, "Sandy Mann"
<[email protected]> 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

[Keith Russell]

On Thu, 27 Jul 2006 00:09:50 +0100, "Bob Phillips"
<[email protected]> 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