Thread: Number rows in a dynamic range

Hi,
How can I to count up quantity of lines in a dynamic range.
for example:

ColumnA; ColumnB
Name1; -
1;20
2;30
3;15
.......
.......
10; 20
Name2; -
1;10
2;15
3;4
4;8
.....


The number of lines between Name1 and Name2 is not known and the number of lines in a dynamic range can vary.
I need Sum(Name1;Name2) - (85)

Thank you

Hi!

Try this:

Assume Name1 is in cell A2.

=SUM(B2:INDEX(B2:B65536,MATCH("Name2",A2:A65536,0)-1))

I don't know how large your range is so I used to row 65536. Adjust to fit
your needs.

Biff

"fishmen" <fishmen.27ahob_1146756902.6197@excelforum-nospam.com> wrote in
message news:fishmen.27ahob_1146756902.6197@excelforum-nospam.com...
>
> Hi,
> How can I to count up quantity of lines in a dynamic range.
> for example:
>
> ColumnA; ColumnB
> Name1; -
> 1;20
> 2;30
> 3;15
> ......
> ......
> 10; 20
> Name2; -
> 1;10
> 2;15
> 3;4
> 4;8
> ....
>
>
> The number of lines between Name1 and Name2 is not known and the number
> of lines in a dynamic range can vary.
> I need Sum(Name1;Name2) - (85)
>
> Thank you
>
>
> --
> fishmen
> ------------------------------------------------------------------------
> fishmen's Profile:
> http://www.excelforum.com/member.php...o&userid=34120
> View this thread: http://www.excelforum.com/showthread...hreadid=538921
>

Thank you very much!!!

Originally Posted by Biff

Hi!

Try this:

Assume Name1 is in cell A2.

=SUM(B2:INDEX(B2:B65536,MATCH("Name2",A2:A65536,0)-1))

I don't know how large your range is so I used to row 65536. Adjust to fit
your needs.

Biff

"fishmen" <fishmen.27ahob_1146756902.6197@excelforum-nospam.com> wrote in
message news:fishmen.27ahob_1146756902.6197@excelforum-nospam.com...
>
> Hi,
> How can I to count up quantity of lines in a dynamic range.
> for example:
>
> ColumnA; ColumnB
> Name1; -
> 1;20
> 2;30
> 3;15
> ......
> ......
> 10; 20
> Name2; -
> 1;10
> 2;15
> 3;4
> 4;8
> ....
>
>
> The number of lines between Name1 and Name2 is not known and the number
> of lines in a dynamic range can vary.
> I need Sum(Name1;Name2) - (85)
>
> Thank you
>
>
> --
> fishmen
> ------------------------------------------------------------------------
> fishmen's Profile:
> http://www.excelforum.com/member.php...o&userid=34120
> View this thread: http://www.excelforum.com/showthread...hreadid=538921
>

You're welcome!

Biff

"fishmen" <fishmen.27bl10_1146807902.8082@excelforum-nospam.com> wrote in
message news:fishmen.27bl10_1146807902.8082@excelforum-nospam.com...
>
> Thank you very much!!!
>
>
> Biff Wrote:
>> Hi!
>>
>> Try this:
>>
>> Assume Name1 is in cell A2.
>>
>> =SUM(B2:INDEX(B2:B65536,MATCH("Name2",A2:A65536,0)-1))
>>
>> I don't know how large your range is so I used to row 65536. Adjust to
>> fit
>> your needs.
>>
>> Biff
>>
>> "fishmen" <fishmen.27ahob_1146756902.6197@excelforum-nospam.com> wrote
>> in
>> message news:fishmen.27ahob_1146756902.6197@excelforum-nospam.com...
>> >
>> > Hi,
>> > How can I to count up quantity of lines in a dynamic range.
>> > for example:
>> >
>> > ColumnA; ColumnB
>> > Name1; -
>> > 1;20
>> > 2;30
>> > 3;15
>> > ......
>> > ......
>> > 10; 20
>> > Name2; -
>> > 1;10
>> > 2;15
>> > 3;4
>> > 4;8
>> > ....
>> >
>> >
>> > The number of lines between Name1 and Name2 is not known and the
>> number
>> > of lines in a dynamic range can vary.
>> > I need Sum(Name1;Name2) - (85)
>> >
>> > Thank you
>> >
>> >
>> > --
>> > fishmen
>> >
>> ------------------------------------------------------------------------
>> > fishmen's Profile:
>> > http://www.excelforum.com/member.php...o&userid=34120
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=538921
>> >
>
>
> --
> fishmen
> ------------------------------------------------------------------------
> fishmen's Profile:
> http://www.excelforum.com/member.php...o&userid=34120
> View this thread: http://www.excelforum.com/showthread...hreadid=538921
>