using last available row in a formula

[mike.wilson8]

A couple of days ago, somone was able to help me count the number of
unique records + only count the value if it starts with "AM".


See below.


=SUMPRODUCT(SUBTOTAL(3,OFFSET(U2:U999,ROW(U2:U999)-ROW(U2),0,1)),--(U2:U999­="AM"))



Now my next task is to see if the formula above can be tweaked, where
U2:U999 isn't predefined, but instead start on row U2 and go to the
last available row in column U.


Any feedback on this would be great.


Thanks,
Mike

 

[Roger Govier]

Hi Mike

You could use a helper cell to work out the address of the last entry in
column U, say in cell X1

=CELL("address",INDEX(U2:U65536,LOOKUP(2,1/(1-ISBLANK(U2:U65536)),ROW(U2:U65536)-ROW(A1)+1)))

Then modify your formula to take an Indirect address using the value
calculated in X1

=SUMPRODUCT(SUBTOTAL(3,OFFSET(INDIRECT("U2:"&X1),ROW(INDIRECT("U2:"&X1)-ROW(U2),0,1)),--(INDIRECT("U2:"&X1)­="AM")


Regards

Roger Govier

 

[Guest]

if the range you are interested in will always fall between U2 and U999

AND IF (critically important)

the entries are contiguous

create a range name such as Col_U_data defined as

=OFFSET(Sheet2!$U$2,0,0,COUNTA(Sheet2!$U$2:$U$999),1)

you should then be able to substitute Col_U_data for U2:u999 throughout your
formula

 

[Domenic]

Here's another way...

First, define a dynamic range...

Insert > Name > Define

Name: Range

Refers to:

=Sheet1!$U$2:INDEX(Sheet1!$U$2:$U$65536,MATCH(REPT("z",255),Sheet1!$U$2:$
U$65536))

Click Ok

Then use the following formula...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),--(R
ange="AM"))

Hope this helps!

 

[Guest]

you could use a custom function

Function LastRow(Col As Integer) As Long
Application.Volatile True
With Application.Caller.Parent
LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
End With
End Function


replace U2:U999 with
Indirect("U2:U"&Lastrow(21))

 

[mike.wilson8]

Roger,

The =CELL formula takes just fine, however, I'm rcving a formula error
on the =sumproducts.

Any ideas?

Thanks,
Mike

 

[Roger Govier]

Hi Mike

The solution I posted was very clumsy. I wasn't thinking very clearly at
the time.
Domenic posted a solution an hour or so after me which is much the
better way to go.
In case you missed it, I have copied it below

Here's another way...
First, define a dynamic range...
Insert > Name > Define
Name: Range
Refers to:

=Sheet1!$U$2:INDEX(Sheet1!$U$2:$U$65536,MATCH(REPT("z",255),Sheet1!$U$2:$U$65536))
Click Ok
Then use the following formula...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),--(Range="AM"))

I would go with this solution if I were you.

Regards

Roger Govier