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.99 999999999999E+307,Sheet
1!$C$2:$C$65536))
Click Add
Name: ColumnC
Refers to:
=Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$65536,MATCH(9.99 999999999999E+307,Sheet
1!$C$2:$C$65536))
Click Ok
Then use the following formula...
=SUMPRODUCT(--(ColumnL="LDC"),ColumnC)
Hope this helps!
In article ,
"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!
|