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