Counting Consecutive Cells

Thanks for trying to figure this out.

Forget everything I just said. Let's start begin this way:

Example 1:
Count the number of times A is in the series A1:E5. You should Count 5.

Col A Col B Col C Col D Col E
Row1 A A
Row2 A
Row3 A
Row4 A
Row5

Example 2:
Now Count the number of consecutive times A appears in the series, starting
from ColA and finishing at ColE. The key word is "consecutive." You should
could count 2. This is because there is no "A" in Col C. The formula should
stop counting everything after the break in the series.

Col A Col B Col C Col D Col E
Row1 A
Row2 A
Row3 A
Row4 A
Row5

My question is, what formula in G1 will count the consecutive number of "As"
in the series A1:E5. That is, what formula will return a value of 2.

Thanks for taking a look...

(this is easily solvable via a sorting macro or multiple vlookups in A1:E5,
but I wanted to see if it's possible in the way described above...)

I'm not sure this is what you're looking for, but try the following...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then, try...

=LOOKUP(BigNum,CHOOSE({1,2},COLUMNS(A1:E5),MATCH(0,MMULT({1,1,1,1,1},--(A
1:E5="A")),0)-1))

In article <FCFF5521-C0EC-42C5-B883-1CEEB055D778@microsoft.com>,
SteveC <SteveC@discussions.microsoft.com> wrote:

> Thanks for trying to figure this out.
>
> Forget everything I just said. Let's start begin this way:
>
> Example 1:
> Count the number of times A is in the series A1:E5. You should Count 5.
>
> Col A Col B Col C Col D Col E
> Row1 A A
> Row2 A
> Row3 A
> Row4 A
> Row5
>
> Example 2:
> Now Count the number of consecutive times A appears in the series, starting
> from ColA and finishing at ColE. The key word is "consecutive." You should
> could count 2. This is because there is no "A" in Col C. The formula should
> stop counting everything after the break in the series.
>
> Col A Col B Col C Col D Col E
> Row1 A
> Row2 A
> Row3 A
> Row4 A
> Row5
>
> My question is, what formula in G1 will count the consecutive number of "As"
> in the series A1:E5. That is, what formula will return a value of 2.
>
> Thanks for taking a look...
>
> (this is easily solvable via a sorting macro or multiple vlookups in A1:E5,
> but I wanted to see if it's possible in the way described above...)

Make that...

=LOOKUP(BigNum,CHOOSE({1,2},COLUMNS(A1:E5),MATCH(0,MMULT(COLUMN(A1:E5)^0,
--(A1:E5="A")),0)-1))

In article <domenic22-024EAC.20280414062006@msnews.microsoft.com>,
Domenic <domenic22@sympatico.ca> wrote:

> I'm not sure this is what you're looking for, but try the following...
>
> Insert > Name > Define
>
> Name: BigNum
>
> Refers to:
>
> =9.99999999999999E+307
>
> Click Ok
>
> Then, try...
>
> =LOOKUP(BigNum,CHOOSE({1,2},COLUMNS(A1:E5),MATCH(0,MMULT({1,1,1,1,1},--(A
> 1:E5="A")),0)-1))
>
> In article <FCFF5521-C0EC-42C5-B883-1CEEB055D778@microsoft.com>,
> SteveC <SteveC@discussions.microsoft.com> wrote:
>
> > Thanks for trying to figure this out.
> >
> > Forget everything I just said. Let's start begin this way:
> >
> > Example 1:
> > Count the number of times A is in the series A1:E5. You should Count 5.
> >
> > Col A Col B Col C Col D Col E
> > Row1 A A
> > Row2 A
> > Row3 A
> > Row4 A
> > Row5
> >
> > Example 2:
> > Now Count the number of consecutive times A appears in the series, starting
> > from ColA and finishing at ColE. The key word is "consecutive." You
> > should
> > could count 2. This is because there is no "A" in Col C. The formula
> > should
> > stop counting everything after the break in the series.
> >
> > Col A Col B Col C Col D Col E
> > Row1 A
> > Row2 A
> > Row3 A
> > Row4 A
> > Row5
> >
> > My question is, what formula in G1 will count the consecutive number of
> > "As"
> > in the series A1:E5. That is, what formula will return a value of 2.
> >
> > Thanks for taking a look...
> >
> > (this is easily solvable via a sorting macro or multiple vlookups in A1:E5,
> > but I wanted to see if it's possible in the way described above...)

Domenic, thanks! That works if I input "A" anywhere in the range A1:E5.

Is there a way to make it work for any text value? It doesn't work with "B"
or "Apples", for example.

Thanks very much!
SteveC


"Domenic" wrote:

> Make that...
>
> =LOOKUP(BigNum,CHOOSE({1,2},COLUMNS(A1:E5),MATCH(0,MMULT(COLUMN(A1:E5)^0,
> --(A1:E5="A")),0)-1))
>
> In article <domenic22-024EAC.20280414062006@msnews.microsoft.com>,
> Domenic <domenic22@sympatico.ca> wrote:
>
> > I'm not sure this is what you're looking for, but try the following...
> >
> > Insert > Name > Define
> >
> > Name: BigNum
> >
> > Refers to:
> >
> > =9.99999999999999E+307
> >
> > Click Ok
> >
> > Then, try...
> >
> > =LOOKUP(BigNum,CHOOSE({1,2},COLUMNS(A1:E5),MATCH(0,MMULT({1,1,1,1,1},--(A
> > 1:E5="A")),0)-1))
> >
> > In article <FCFF5521-C0EC-42C5-B883-1CEEB055D778@microsoft.com>,
> > SteveC <SteveC@discussions.microsoft.com> wrote:
> >
> > > Thanks for trying to figure this out.
> > >
> > > Forget everything I just said. Let's start begin this way:
> > >
> > > Example 1:
> > > Count the number of times A is in the series A1:E5. You should Count 5.
> > >
> > > Col A Col B Col C Col D Col E
> > > Row1 A A
> > > Row2 A
> > > Row3 A
> > > Row4 A
> > > Row5
> > >
> > > Example 2:
> > > Now Count the number of consecutive times A appears in the series, starting
> > > from ColA and finishing at ColE. The key word is "consecutive." You
> > > should
> > > could count 2. This is because there is no "A" in Col C. The formula
> > > should
> > > stop counting everything after the break in the series.
> > >
> > > Col A Col B Col C Col D Col E
> > > Row1 A
> > > Row2 A
> > > Row3 A
> > > Row4 A
> > > Row5
> > >
> > > My question is, what formula in G1 will count the consecutive number of
> > > "As"
> > > in the series A1:E5. That is, what formula will return a value of 2.
> > >
> > > Thanks for taking a look...
> > >
> > > (this is easily solvable via a sorting macro or multiple vlookups in A1:E5,
> > > but I wanted to see if it's possible in the way described above...)
>

If your data does not contain numerical values, you could simply
replace...

="A"

with

<>""

Is this the case?

In article <94C38223-B0B9-4827-ADAC-EEC68867CC0F@microsoft.com>,
SteveC <SteveC@discussions.microsoft.com> wrote:

> Domenic, thanks! That works if I input "A" anywhere in the range A1:E5.
>
> Is there a way to make it work for any text value? It doesn't work with "B"
> or "Apples", for example.
>
> Thanks very much!
> SteveC

Ok, that works now for one type of text value

But now when I had more than one text value, it doesn't distinguish between
the text values.

For example, Cell G22 below should return a value of 2, but it's currently
returning a value of 4.

ColA | ColB | ColC | Col D | ColE | ColF | Col G|
Apples | Bears | Apples | | | | 4
Bears | Apples | | Apples | | | 2
| | | Bears |

thanks a lot...

It seems to me that Columns A through D each have at least one text
value. In this situation, I understood the correct result to be 4. And
if we deleted Apples from Column C, leaving Column C with no text
values, I understood the correct result to be 2. Isn't this correct?
If not, you'll need to clarify...

In article <C95FF17A-8E31-48E0-9C14-36A023EFB28B@microsoft.com>,
SteveC <SteveC@discussions.microsoft.com> wrote:

> Ok, that works now for one type of text value
>
> But now when I had more than one text value, it doesn't distinguish between
> the text values.
>
> For example, Cell G22 below should return a value of 2, but it's currently
> returning a value of 4.
>
> ColA | ColB | ColC | Col D | ColE | ColF | Col G|
> Apples | Bears | Apples | | | | 4
> Bears | Apples | | Apples | | | 2
> | | | Bears |
>
> thanks a lot...

Ok, that works now for one type of text value

But now when I had more than one text value, it doesn't distinguish between
the text values.

For example, Cell G22 below should return a value of 2, but it's currently
returning a value of 4.

ColA | ColB | ColC | Col D | ColE | ColF | Col G|
Apples | Bears | Apples | | | | 4
Bears | Apples | | Apples | | | 2
| | | Bears |

thanks a lot...

Ok, that works now for one type of text value

But now when I had more than one text value, it doesn't distinguish between
the text values.

For example, Cell G22 below should return a value of 2, but it's currently
returning a value of 4.

ColA | ColB | ColC | Col D | ColE | ColF | Col G|
Apples | Bears | Apples | | | | 4
Bears | Apples | | Apples | | | 2
| | | Bears |

thanks a lot...