Counting Consecutive Cells

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

Yes, that is correct... thanks!

"Domenic" wrote:

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