formula to use when no. of rows changes dynamically

[confused]

Hi,

how do I refer to a column when the number of rows may change dynamically?
e.g column A with 12 rows of data would be a1:A12, with dynamic number of
rows it would be A1:A??

I am using SumProduct to add up those rows which meet certain conditions.
But the number of rows in the source area changes, so how can I get my
sumproduct formula to work as the number of rows changes?
eg my pseudo code is:

=SUMPRODUCT((L2:L"number of rows" = "LDC")*(C2:C"number of rows"))

I know that COUNTA can be used to return the number of non empty rows but
cant work out the syntax to use this, e.g. this is wrong:
=SUMPRODUCT(L2:LCOUNTA(L:L)= "LDC")*(C2:LCOUNTA(L:L))

thanks to anyone who can help or advise!

[arno]

Hi confused,

> how do I refer to a column when the number of rows may change
> dynamically? e.g column A with 12 rows of data would be a1:A12, with
> dynamic number of rows it would be A1:A??
>

> I know that COUNTA can be used to return the number of non empty rows

good! combine Counta() with the OFFSET()-function. eg.

=offset(a1,0,0,counta(whatever), numberofcolums)

you can use this formula to specify the data range of named ranges eg.
"myrange", to sum up everything you could use then a formula like
=sum(myrange)

read online help to the offset-function.

arno

[Domenic]

One way would be to define dynamic ranges...

Insert > Name > Define

Name: ColumnL

Refers to:
=Sheet1!$L$2:INDEX(Sheet1!$L$2:$L$65536,MATCH(9.99999999999999E+307,Sheet
1!$C$2:$C$65536))

Click Add

Name: ColumnC

Refers to:
=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$65536,MATCH(9.99999999999999E+307,Sheet
1!$C$2:$C$65536))

Click Ok

Then use the following formula...

=SUMPRODUCT(--(ColumnL="LDC"),ColumnC)

Hope this helps!

In article <[email protected]>,
"confused" <[email protected]> wrote:

> Hi,
>
> how do I refer to a column when the number of rows may change dynamically?
> e.g column A with 12 rows of data would be a1:A12, with dynamic number of
> rows it would be A1:A??
>
> I am using SumProduct to add up those rows which meet certain conditions.
> But the number of rows in the source area changes, so how can I get my
> sumproduct formula to work as the number of rows changes?
> eg my pseudo code is:
>
> =SUMPRODUCT((L2:L"number of rows" = "LDC")*(C2:C"number of rows"))
>
> I know that COUNTA can be used to return the number of non empty rows but
> cant work out the syntax to use this, e.g. this is wrong:
> =SUMPRODUCT(L2:LCOUNTA(L:L)= "LDC")*(C2:LCOUNTA(L:L))
>
> thanks to anyone who can help or advise!

[Simon Letten]

The COUNTA function will tell you how many non-empty rows there are but you
need to wrap that in something that will give a valid range, e.g.
SUM(INDIRECT("L2:L"&COUNTA(L:L)))

--
HTH

Simon


"confused" wrote:

> Hi,
>
> how do I refer to a column when the number of rows may change dynamically?
> e.g column A with 12 rows of data would be a1:A12, with dynamic number of
> rows it would be A1:A??
>
> I am using SumProduct to add up those rows which meet certain conditions.
> But the number of rows in the source area changes, so how can I get my
> sumproduct formula to work as the number of rows changes?
> eg my pseudo code is:
>
> =SUMPRODUCT((L2:L"number of rows" = "LDC")*(C2:C"number of rows"))
>
> I know that COUNTA can be used to return the number of non empty rows but
> cant work out the syntax to use this, e.g. this is wrong:
> =SUMPRODUCT(L2:LCOUNTA(L:L)= "LDC")*(C2:LCOUNTA(L:L))
>
> thanks to anyone who can help or advise!