using last available row in a formula
 

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.


=3DSUMPRODUCT(SUBTOTAL(3,OFFSET(U2:U999,ROW(U2:U99 9)-ROW(U2),0,1)),--(U2:U9=
99=AD=3D"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.=20


Thanks,=20
Mike

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),R OW(INDIRECT("U2:"&X1)-ROW(U2),0,1)),--(INDIRECT("U2:"&X1)*="AM")


Regards

Roger Govier


wrote:
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

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


" wrote:

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

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!

In article .com,
wrote:

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

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



" wrote:

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,

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

Any ideas?

Thanks,
Mike

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(REP T("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



wrote:

Roger,

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

Any ideas?

Thanks,
Mike