CountBlank for all Names in a Column, and another trick

  • Thread starter Thread starter Guest
  • Start date Start date
G

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