RankA RankB
Apples 3 1
Apples
Apples 5 3
Apples 4 2
Apples
Pears
Pears 2 1
Pears 3 2

The Column RankB is counting the number of blank cells for all Apples, and
subracting that number from the column labled RankA. Any clue as to the
right formula? Thanks..

Try:

=IF(B2="","",B2-SUMPRODUCT(--($A$2:$A$9=A2),--(($B$2:$B$9=""))))

or

=IF(B2="","",B2-SUMPRODUCT(--($A$2:$A$9=A2),--(ISBLANK(($B$2:$B$9)))))

HTH

"SteveC" wrote:


RankA RankB
Apples 3 1
Apples
Apples 5 3
Apples 4 2
Apples
Pears
Pears 2 1
Pears 3 2

The Column RankB is counting the number of blank cells for all Apples, and
subracting that number from the column labled RankA. Any clue as to the
right formula? Thanks..

That works great...

Look at the monster you have helped me create:

=IF(ISERROR((SUMPRODUCT(--($A$13:$A$3000=$A21))+1)-(SUMPRODUCT(--($A$13:$A$3000=$A21),--(AI21<AI$13:AI$3000))+1)-IF(AI21="","",SUMPRODUCT(--($A$13:$A$3000=A21),--(($AI$13:$AI$3000=""))))),"",(SUMPRODUCT(--($A$13:$A$3000=$A21))+1)-(SUMPRODUCT(--($A$13:$A$3000=$A21),--(AI21<AI$13:AI$3000))+1)-IF(AI21="","",SUMPRODUCT(--($A$13:$A$3000=A21),--(($AI$13:$AI$3000="")))))

any ideas to cut that down, let me know!

thanks man...

SteveC