CountBlank for all Names in a Column, and another trick

[Guest]

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

 

[Guest]

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

 

[Guest]

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