Error while converting text range to numeric range using Excel looped/nested function

Question

Sorting

I need help in the below formula that I am using to number the above table in the "Sorted Numbering" column. The formula that I am using basis which the "Sorted Numbering" column derives its value is:

=IF(COUNTIFS($B$2:$B$11,B2,$C$2:$C$11,C2)=1,"NA",IF(COUNTIFS($B$2:$B$11,B2,$C$2:$C$11,C2,$D$2:$D$11,D2)>1,COUNTIFS($B$2:$B$11,B2,$C$2:$C$11,C2,$D$2:$D$11,D2,$H$2#,"<"&E2)+1,COUNTIFS($B$2:$B$11,B2,$C$2:$C$11,C2,$D$2:$D$11,"<"&D2)+1))

In this there is a reference to an array list (I suppose) in cell H2 seen as "$H$2#". The formula in the H2 cell is:

=VALUE($E$2:$E$11)

which basically converts the text values in column E2:E11 from text values to Numeric values. I want to use this formula in the cell H2 directly in the main formula (the first formula) without without creating a reference to cell H2. When I try to replace the "$H$2#" to "VALUE($E$2:$E$11)" in the main formula, I get the below error.

ErrorMessage

Is there a way to get the entire formula in one cell itself without creating a reference to another cell.

Answer 1

You can replace the middle COUNTIFS with SUM as follows:

=IF( COUNTIFS( $B$2:$B$11, B2,
                        $C$2:$C$11, C2) = 1,
    "NA",
    IF( COUNTIFS( $B$2:$B$11, B2,
                               $C$2:$C$11, C2,
                               $D$2:$D$11, D2 ) >1,
         SUM( ($B$2:$B$11=B2)*($C$2:$C$11=C2)*($D$2:$D$11=D2)*($E$2:$E$11<E2) )+1,
         COUNTIFS( $B$2:$B$11, B2,
                              $C$2:$C$11, C2,
                              $D$2:$D$11, "<"&D2 ) + 1 ) )

so that

COUNTIFS($B$2:$B$11,B2,$C$2:$C$11,C2,$D$2:$D$11,D2,$H$2#,"<"&E2)+1

is replaced with

SUM( ($B$2:$B$11=B2)*($C$2:$C$11=C2)*($D$2:$D$11=D2)*($E$2:$E$11<E2) )+1