SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(Cn<C$2:C$7))
=0 (there are no numbers less than Cn because Cn is the max)

Not phrased very well!

SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(Cn<C$2:C$7))

=0 (because Cn is *not less than* any numbers in C2:C7...)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Would have thought that would return 0 or 1...

If Cn is the max that meets the criteria (month=7) then:

SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(Cn<C$2:C$7))

=0 (there are no numbers less than Cn because Cn is the max)

So we add 1:

SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(Cn<C$2:C$7))+1

--
Biff
Microsoft Excel MVP


"Sean Timmons" wrote in message
...
Very nice. So --(C2<C$2:C$7) returns number of values greater than C2...
Would have thought that would return 0 or 1...

"T. Valko" wrote:

Assuming the dates are all within the same year:

=IF(MONTH(A2)<7,"",SUMPRODUCT(--(MONTH(A$2:A$7)=7),--(C2<C$2:C$7))+1)

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Sean Timmons" wrote in message
...
So, I have a worksheet with a few thousand rows.
There is one row per month per employee

5/31/09 Bob A 2.5
6/30/09 Bob A 1.9
7/31/09 Bob A 2.3
5/31/09 John S 1.6
6/30/09 John S 2.0
7/31/09 John S 2.9
etc.

I would like to rank the most recent score (2.3 for Bob A vs. 2.9 for
John
S), and return a 1 on John S.'s July line and a 2 on Bob A.s July
line,
with
blanks in the others.

I can simply use MAX() to get the most current date, but not sure how
to
get
my RANK to only rank those scores in July. Always getting a 3 for Bob
a.
in
the above...