Can this VB code (courtesy of Bernie) be modified to accept 2 or more
ranges?

Function RankIf(RankCell As Range, _
RankRange As Range, _
CritRange As Range, _
Criteria As Variant, _
Optional DescOrder As Boolean = True) As Integer


'=RankIf(A1,$A$1:$A$10,$B$1:$B$10,"A", True)
'
'Where A1 has the number to be ranked,
'A1:A10 has the numbers against which A1 is to be ranked
'B1:B10 have the criteria
'"A" is the criteria (which can also be a Cell reference)
'True means smaller values get lower rank numbers, False would be the
'opposite
'
'The function can be copied just like a regular function, and will
return 0
'if the number to be ranked doesn't meet the criteria.


Dim i As Integer
Dim myRange As Range
On Error GoTo notRanked
For i = 1 To CritRange.Count
If CritRange(i) = Criteria Then
If myRange Is Nothing Then
Set myRange = RankRange(i)
Else
Set myRange = Union(myRange, RankRange(i))
End If
End If
Next i
RankIf = Application.WorksheetFunction.Rank(RankCell, myRange,
DescOrder)
Exit Function
notRanked:
RankIf = 0
End Function

-S

S Davis wrote:
Sad to report that the information I am trying to rank is spread across
more than one column.

Right now Im using this formula:

=SUMPRODUCT(--(A:A=A4),--(N4<B:B))+1

... can this be modified to take into account C:C, D:D, and E:E, so
that A4 is ranked out of the results of all of those columns?

S Davis wrote:
Thanks, will do!
-S

wrote:
Hello,

Search for Bernie's function Rankif in this newsgroup.

HTH,
Bernd