Counting Consecutive Cells

This works great: In Cell G2: =MATCH("Off",A4:E4,0)-1

ColA ColB ColC ColD ColE
ColG
Apples Apples Off Apples Off 2
Bannanas Bannanas Bannanas Bannanas Off 4
Coyotes Coyotes Coyotes Off Off 3
Elephants Elephants Elephants Elephants Off 4


Now someone please tell me what formula would work in ColG in the set below,
with ColG referencing ColA? If possible?

ColA ColB ColC ColD ColE
ColG
Apples Off Coyotes Apples Off 2
Bannanas Apples Elephants Bannanas Off 1
Coyotes Coyotes Off Off Off 3
Elephants Elephants Off Elephants Off 4

just curious if this is possible... thanks....

assuming the last one should be 2 not 4

=COUNTIF(A2:E2,"Off")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SteveC" <SteveC@discussions.microsoft.com> wrote in message
news:EB82CBD5-257E-4AC8-B982-1227FE3FF61A@microsoft.com...
> This works great: In Cell G2: =MATCH("Off",A4:E4,0)-1
>
> ColA ColB ColC ColD ColE
> ColG
> Apples Apples Off Apples Off 2
> Bannanas Bannanas Bannanas Bannanas Off 4
> Coyotes Coyotes Coyotes Off Off 3
> Elephants Elephants Elephants Elephants Off 4
>
>
> Now someone please tell me what formula would work in ColG in the set
below,
> with ColG referencing ColA? If possible?
>
> ColA ColB ColC ColD ColE
> ColG
> Apples Off Coyotes Apples Off 2
> Bannanas Apples Elephants Bannanas Off 1
> Coyotes Coyotes Off Off Off 3
> Elephants Elephants Off Elephants Off 4
>
> just curious if this is possible... thanks....
>
>
>
>

Hi, thanks for your response. Looks like that formula is counting "off", but
what I really want is to count whatever is in cell A2 in the range A2:E5,
then return that value in G2. Then consecutively count whatever is in cell
A3 in the range A2:E5, and return that value in G3... etc... thanks very
much for your time... regards, SteveC

"Bob Phillips" wrote:

> assuming the last one should be 2 not 4
>
> =COUNTIF(A2:E2,"Off")
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "SteveC" <SteveC@discussions.microsoft.com> wrote in message
> news:EB82CBD5-257E-4AC8-B982-1227FE3FF61A@microsoft.com...
> > This works great: In Cell G2: =MATCH("Off",A4:E4,0)-1
> >
> > ColA ColB ColC ColD ColE
> > ColG
> > Apples Apples Off Apples Off 2
> > Bannanas Bannanas Bannanas Bannanas Off 4
> > Coyotes Coyotes Coyotes Off Off 3
> > Elephants Elephants Elephants Elephants Off 4
> >
> >
> > Now someone please tell me what formula would work in ColG in the set
> below,
> > with ColG referencing ColA? If possible?
> >
> > ColA ColB ColC ColD ColE
> > ColG
> > Apples Off Coyotes Apples Off 2
> > Bannanas Apples Elephants Bannanas Off 1
> > Coyotes Coyotes Off Off Off 3
> > Elephants Elephants Off Elephants Off 4
> >
> > just curious if this is possible... thanks....
> >
> >
> >
> >
>
>
>

OK, so why is G3, looking for Bananas, only 1, not 2

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SteveC" <SteveC@discussions.microsoft.com> wrote in message
news:3842A90C-FA5B-4A91-9190-6627BEB4CD9C@microsoft.com...
> Hi, thanks for your response. Looks like that formula is counting "off",
but
> what I really want is to count whatever is in cell A2 in the range A2:E5,
> then return that value in G2. Then consecutively count whatever is in
cell
> A3 in the range A2:E5, and return that value in G3... etc... thanks very
> much for your time... regards, SteveC
>
> "Bob Phillips" wrote:
>
> > assuming the last one should be 2 not 4
> >
> > =COUNTIF(A2:E2,"Off")
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "SteveC" <SteveC@discussions.microsoft.com> wrote in message
> > news:EB82CBD5-257E-4AC8-B982-1227FE3FF61A@microsoft.com...
> > > This works great: In Cell G2: =MATCH("Off",A4:E4,0)-1
> > >
> > > ColA ColB ColC ColD ColE
> > > ColG
> > > Apples Apples Off Apples Off 2
> > > Bannanas Bannanas Bannanas Bannanas Off 4
> > > Coyotes Coyotes Coyotes Off Off 3
> > > Elephants Elephants Elephants Elephants Off 4
> > >
> > >
> > > Now someone please tell me what formula would work in ColG in the set
> > below,
> > > with ColG referencing ColA? If possible?
> > >
> > > ColA ColB ColC ColD ColE
> > > ColG
> > > Apples Off Coyotes Apples Off 2
> > > Bannanas Apples Elephants Bannanas Off 1
> > > Coyotes Coyotes Off Off Off 3
> > > Elephants Elephants Off Elephants Off 4
> > >
> > > just curious if this is possible... thanks....
> > >
> > >
> > >
> > >
> >
> >
> >

I undestand your question now. It's because I only want to count
consecutively, left to right. So if Bannanas is only in Col A and Col D, it
only counts 1.

E.g. counting X -- X is always in ColA

X Y Y Y 1
X Y X X 1
X X Y X 2
X X Y Y 2
X X X X 4

Thanks for taking a look...





"Bob Phillips" wrote:

> OK, so why is G3, looking for Bananas, only 1, not 2
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "SteveC" <SteveC@discussions.microsoft.com> wrote in message
> news:3842A90C-FA5B-4A91-9190-6627BEB4CD9C@microsoft.com...
> > Hi, thanks for your response. Looks like that formula is counting "off",
> but
> > what I really want is to count whatever is in cell A2 in the range A2:E5,
> > then return that value in G2. Then consecutively count whatever is in
> cell
> > A3 in the range A2:E5, and return that value in G3... etc... thanks very
> > much for your time... regards, SteveC
> >
> > "Bob Phillips" wrote:
> >
> > > assuming the last one should be 2 not 4
> > >
> > > =COUNTIF(A2:E2,"Off")
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "SteveC" <SteveC@discussions.microsoft.com> wrote in message
> > > news:EB82CBD5-257E-4AC8-B982-1227FE3FF61A@microsoft.com...
> > > > This works great: In Cell G2: =MATCH("Off",A4:E4,0)-1
> > > >
> > > > ColA ColB ColC ColD ColE
> > > > ColG
> > > > Apples Apples Off Apples Off 2
> > > > Bannanas Bannanas Bannanas Bannanas Off 4
> > > > Coyotes Coyotes Coyotes Off Off 3
> > > > Elephants Elephants Elephants Elephants Off 4
> > > >
> > > >
> > > > Now someone please tell me what formula would work in ColG in the set
> > > below,
> > > > with ColG referencing ColA? If possible?
> > > >
> > > > ColA ColB ColC ColD ColE
> > > > ColG
> > > > Apples Off Coyotes Apples Off 2
> > > > Bannanas Apples Elephants Bannanas Off 1
> > > > Coyotes Coyotes Off Off Off 3
> > > > Elephants Elephants Off Elephants Off 4
> > > >
> > > > just curious if this is possible... thanks....
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

.... keeping in mind that the variables that we are matching to Col A are
randomly dispersed or even omitted in Col B, Col C and Col D, and we are
counting consecutively left to right...

Then I am confused where any of these numbers come form

ColA |ColB |ColC |ColD |ColE|ColG
Apples |Off |Coyotes |Apples |Off |2
Bannanas |Apples |Elephants|Bannanas|Off |1
Coyotes |Coyotes |Off |Off |Off |3
Elephants|Elephants|Off |Elephants|Off |4

Whys isn't it 1,1,1,1?


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SteveC" <SteveC@discussions.microsoft.com> wrote in message
news:9CA3C07B-AC85-4429-8B7D-86B4FF612BDC@microsoft.com...
> ... keeping in mind that the variables that we are matching to Col A are
> randomly dispersed or even omitted in Col B, Col C and Col D, and we are
> counting consecutively left to right...