Ranking based on conditions
 

In column A I have unique IDs (10001 up to 19999).
In column B I have countries,
In column C I have fruit names,
In column D I have values (between 1 and 10000)

If I have a unique ID - lets say ID 14592
It's corresponding country is Australia, and corresponding fruit is Pear,
and corresponding value is 1000

I want to know its rank in its class.
- its rank for all Pears
- its rank for all Australians
- its rank for all Australian Pears.

Can someone help with the formula for this? Say the ID (14592) is entered
in F1

Thanks in advance!

Daniel

Ranking based on conditions
 

Assuming that the fruit name is also in F2

=IF(C1<$F$2,"",MATCH(D1,LARGE(IF($C$1:$C$25="Pear s",$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($C$1:$C$2 5,$F$2)))),0))

=IF(A1<$F$1,"",MATCH(D1,LARGE(IF(($A$1:$A$25=$F$1 ),$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($A$1:$A$25 ,$F$1)))),0))

=IF(OR(C1<$F$2,A1<$F$1),"",MATCH(D1,LARGE(IF(($C $1:$C$25=$F$2)*($A$1:$A$25=$F$1),$D$1:$D$25),ROW(I NDIRECT("1:"&SUM(($C$1:$C$25=$F$2)*($A$1:$A$25=$F$ 1))))),0))

all array formulae

(and not a SUMPRODUCT in sight)


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Daniel Bonallack" wrote in
message ...
In column A I have unique IDs (10001 up to 19999).
In column B I have countries,
In column C I have fruit names,
In column D I have values (between 1 and 10000)

If I have a unique ID - lets say ID 14592
It's corresponding country is Australia, and corresponding fruit is Pear,
and corresponding value is 1000

I want to know its rank in its class.
- its rank for all Pears
- its rank for all Australians
- its rank for all Australian Pears.

Can someone help with the formula for this? Say the ID (14592) is entered
in F1

Thanks in advance!

Daniel

Ranking based on conditions
 

Hi Bob

This returned me with a blank cell - would it be possible to send you my
country/fruit example?

regards
Daniel


"Bob Phillips" wrote:

Assuming that the fruit name is also in F2

=IF(C1<$F$2,"",MATCH(D1,LARGE(IF($C$1:$C$25="Pear s",$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($C$1:$C$2 5,$F$2)))),0))

=IF(A1<$F$1,"",MATCH(D1,LARGE(IF(($A$1:$A$25=$F$1 ),$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($A$1:$A$25 ,$F$1)))),0))

=IF(OR(C1<$F$2,A1<$F$1),"",MATCH(D1,LARGE(IF(($C $1:$C$25=$F$2)*($A$1:$A$25=$F$1),$D$1:$D$25),ROW(I NDIRECT("1:"&SUM(($C$1:$C$25=$F$2)*($A$1:$A$25=$F$ 1))))),0))

all array formulae

(and not a SUMPRODUCT in sight)


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Daniel Bonallack" wrote in
message ...
In column A I have unique IDs (10001 up to 19999).
In column B I have countries,
In column C I have fruit names,
In column D I have values (between 1 and 10000)

If I have a unique ID - lets say ID 14592
It's corresponding country is Australia, and corresponding fruit is Pear,
and corresponding value is 1000

I want to know its rank in its class.
- its rank for all Pears
- its rank for all Australians
- its rank for all Australian Pears.

Can someone help with the formula for this? Say the ID (14592) is entered
in F1

Thanks in advance!

Daniel

Ranking based on conditions
 

Of course, juts pop it in the post.

Bob


"Daniel Bonallack" wrote in
message ...
Hi Bob

This returned me with a blank cell - would it be possible to send you my
country/fruit example?

regards
Daniel


"Bob Phillips" wrote:

Assuming that the fruit name is also in F2

=IF(C1<$F$2,"",MATCH(D1,LARGE(IF($C$1:$C$25="Pear s",$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($C$1:$C$2 5,$F$2)))),0))

=IF(A1<$F$1,"",MATCH(D1,LARGE(IF(($A$1:$A$25=$F$1 ),$D$1:$D$25),ROW(INDIRECT("1:"&COUNTIF($A$1:$A$25 ,$F$1)))),0))

=IF(OR(C1<$F$2,A1<$F$1),"",MATCH(D1,LARGE(IF(($C $1:$C$25=$F$2)*($A$1:$A$25=$F$1),$D$1:$D$25),ROW(I NDIRECT("1:"&SUM(($C$1:$C$25=$F$2)*($A$1:$A$25=$F$ 1))))),0))

all array formulae

(and not a SUMPRODUCT in sight)


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Daniel Bonallack" wrote in
message ...
In column A I have unique IDs (10001 up to 19999).
In column B I have countries,
In column C I have fruit names,
In column D I have values (between 1 and 10000)

If I have a unique ID - lets say ID 14592
It's corresponding country is Australia, and corresponding fruit is
Pear,
and corresponding value is 1000

I want to know its rank in its class.
- its rank for all Pears
- its rank for all Australians
- its rank for all Australian Pears.

Can someone help with the formula for this? Say the ID (14592) is
entered
in F1

Thanks in advance!

Daniel

Ranking based on conditions
 

Daniel,

Please use the gmail account mentioned in my postings, I am having horrific
spam problems in my main accounts at the moment.

Bob