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