SOLVED

Rank Column

Copper Contributor

=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

@Able1974 

Perhaps

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

@Able1974 

In L3 here

image.png

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. 

@Able1974 

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

 

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Able1974 

Perhaps

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

View solution in original post