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