Count If Formula Help

[mlbdc2012]

I have a count if formula in my worksheet that produces a number rank for one column based on criteria from other columns in the worksheet. I'm running into a problem with the formula as it works great for all players with a current rank, but for players without a rank it's giving them a 1 instead of just leaving it blank.

Example:

I have a list of players, which all have an Overall Rank listed in column E. I have two additional columns (G and I) which break all the players into two separate leagues (AL/NL) based on what league they play in (Column D). The formula gives a rank to all players solely from the AL or NL based on the rank in Column E.

For players without a rank in column E, their rank in column G and I should then also be blank. Cell I992, should be blank but for some reason it is giving that player a 1, same with the rest of the guys below him. Can anyone help with this? Also, does anyone know why it takes so long for excel to calculate all the formulas?

My worksheet is attached, thanks!

[daffodil11]

Your IF condition only states if D = G, do something and add 1. The 'something' is zero so the result is 1.

Add another condition to your test and make them both be true.

G4:

=IF(AND($D4=G$2,E4<>""),COUNTIFS($D$4:$D$1010,$D4,$E$4:$E$1010,"<"&E4)+1,"")

[mlbdc2012]

Your IF condition only states if D = G, do something and add 1. The 'something' is zero so the result is 1.

Add another condition to your test and make them both be true.

G4:

=IF(AND($D4=G$2,E4<>""),COUNTIFS($D$4:$D$1010,$D4,$E$4:$E$1010,"<"&E4)+1,"")

I entered in your formula and it's still giving a 1 for cells G1002:G1010. Any ideas?

[daffodil11]

I see. The cells are not blank, but contain a single space.

=len(e1002) = 1

=code(e1002) = 32

ASCII code 32 = Space Key

=IF(AND($D1002=G$2,E1002<>" "),COUNTIFS($D$4:$D$1010,$D1002,$E$4:$E$1010,"<"&E1002)+1,"")

or

=IF(AND($D1002=G$2,E1002<>char(32)),COUNTIFS($D$4:$D$1010,$D1002,$E$4:$E$1010,"<"&E1002)+1,"")

[mlbdc2012]

I see. The cells are not blank, but contain a single space.

=len(e1002) = 1

=code(e1002) = 32

ASCII code 32 = Space Key

=IF(AND($D1002=G$2,E1002<>" "),COUNTIFS($D$4:$D$1010,$D1002,$E$4:$E$1010,"<"&E1002)+1,"")

or

=IF(AND($D1002=G$2,E1002<>char(32)),COUNTIFS($D$4:$D$1010,$D1002,$E$4:$E$1010,"<"&E1002)+1,"")

That worked! Thanks a bunch