Dec 21 2021 08:09 AM
=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.
Dec 21 2021 08:59 AM
SolutionPerhaps
=SUMPRODUCT(( $K$3:$K$7<$K3 )*( $K$3:$K$7<>0 ) / COUNTIF( $K$3:$K$7,$K$3:$K$7 ) )+( $K3<>0 )
Jan 01 2022 02:17 PM
Jan 02 2022 01:07 AM
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
Jan 02 2022 03:57 PM
@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.
Jan 03 2022 01:18 AM
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) )
Dec 21 2021 08:59 AM
SolutionPerhaps
=SUMPRODUCT(( $K$3:$K$7<$K3 )*( $K$3:$K$7<>0 ) / COUNTIF( $K$3:$K$7,$K$3:$K$7 ) )+( $K3<>0 )