 SOLVED

# Rank Column

=SUMPRODUCT((\$K\$3:\$K\$7<\$K3)/COUNTIF(\$K\$3:\$K\$7,\$K\$3:\$K\$7))+1

This formula works only issue is I need it to exclude zeros when ranking.

6 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Rank Column

Perhaps

``=SUMPRODUCT(( \$K\$3:\$K\$7<\$K3 )*( \$K\$3:\$K\$7<>0 ) / COUNTIF( \$K\$3:\$K\$7,\$K\$3:\$K\$7 ) )+( \$K3<>0 )``

# Re: Rank Column

@Sergei Baklan thank you that worked perfectly.

# Re: Rank Column

I know need to add only certain cells in a row but on occasion those cells may have #N/A, when this occurs, I need those cells to be treated as if they were zero.

# Re: Rank Column

In L3 here it could be

``= IFNA( SUMPRODUCT(( IFNA(\$K\$3:\$K\$7,0)  < \$K3 )*( IFNA(\$K\$3:\$K\$7,0) <> 0 ) / COUNTIF( \$K\$3:\$K\$7, \$K\$3:\$K\$7 ) ) + ( \$K3<>0 ), 0 )``

and drag it down

# Re: Rank Column

@Sergei Baklan I need it for a row not a column. I have attached the work sheet; I am trying to get "Total Points" by adding each event's points.

# Re: Rank Column

On VLOOKUP() you may return zero instead of #N/A if replace

``=VLOOKUP(H3,'Placings - Points Table'!\$A\$2:\$B\$101,2,FALSE)``

on

``=IFNA( VLOOKUP(H3,'Placings - Points Table'!\$A\$2:\$B\$101,2,FALSE), 0)``

Alternatively you may sum as

``=SUM( IFNA( INDEX(I4:AH4, 1, {1,6,11,16,21,26} ), 0)  )``