CountBlank for all Names in a Column, and another trick
 

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..

CountBlank for all Names in a Column, and another trick
 

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..

CountBlank for all Names in a Column, and another trick
 

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