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" 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 valueNext: excel - rows repeat on top