Rank Column

Occasional Contributor



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

6 Replies
best response confirmed by Hans Vogelaar (MVP)



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

@Sergei Baklan thank you that worked perfectly. 

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.


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

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


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

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


=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)  )