Prev: Formula to estimate value
Next: excel - rows repeat on top
From: Richard on 1 Mar 2010 02:11
Hi,

I have a list of 8 intitials in a row (C7 : J7), such as AR, IR, CS, RP etc.
These are 4 golfing pairs.

How do I count the number of times "AR" AND "IR" appear anywhere on row
C7..J7?

E.g.
if both "AR and "IR" appear in range C7 : J7, the result of "1" would be
returned in K7.
if just "AR" appears, without "IR", the result of "0" would be returned in
K7.

I'm stumped - any assistance would be appreciated

Tks - Rick

From: Roger Govier on 1 Mar 2010 03:05
Hi Richard

One way
=MAX(0,SUMPRODUCT(--(C7:J7="AR")+(C7:J7="IR"))-1)

Regards
Roger Govier


Richard wrote:
> Hi,
>
> I have a list of 8 intitials in a row (C7 : J7), such as AR, IR, CS, RP
> etc. These are 4 golfing pairs.
>
> How do I count the number of times "AR" AND "IR" appear anywhere on row
> C7..J7?
>
> E.g.
> if both "AR and "IR" appear in range C7 : J7, the result of "1" would be
> returned in K7.
> if just "AR" appears, without "IR", the result of "0" would be returned
> in K7.
>
> I'm stumped - any assistance would be appreciated
>
> Tks - Rick
From: Rohinikumar on 1 Mar 2010 04:53
Hi May be this can help you.
just click another cell and copy this below format
=COUNTIF(C:C,"AR")


"Richard" wrote:

> Hi,
>
> I have a list of 8 intitials in a row (C7 : J7), such as AR, IR, CS, RP etc.
> These are 4 golfing pairs.
>
> How do I count the number of times "AR" AND "IR" appear anywhere on row
> C7..J7?
>
> E.g.
> if both "AR and "IR" appear in range C7 : J7, the result of "1" would be
> returned in K7.
> if just "AR" appears, without "IR", the result of "0" would be returned in
> K7.
>
> I'm stumped - any assistance would be appreciated
>
> Tks - Rick
>
> .
>
From: Bob Phillips on 1 Mar 2010 08:15
Why would it be there more than once?

Although I don't feel that the spec is clear, here is a shot

=INT(SUM(COUNTIF(C7:J7,{"AR","IR"}))/2)

HTH

Bob

"Richard" <richard.phelan(a)westnet.com.au> wrote in message
news:utyv75QuKHA.4752(a)TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I have a list of 8 intitials in a row (C7 : J7), such as AR, IR, CS, RP
> etc. These are 4 golfing pairs.
>
> How do I count the number of times "AR" AND "IR" appear anywhere on row
> C7..J7?
>
> E.g.
> if both "AR and "IR" appear in range C7 : J7, the result of "1" would be
> returned in K7.
> if just "AR" appears, without "IR", the result of "0" would be returned in
> K7.
>
> I'm stumped - any assistance would be appreciated
>
> Tks - Rick


 | 
Pages: 1
Prev: Formula to estimate value
Next: excel - rows repeat on top