Excel value calculation based on column name-CodePudding

I have a table with column names as postal code and under every postal code there is revenue, but there are some postal s that have letters in them or are empty and that causes my formula to return error.

Idea is to calculate revenue for each region, where region name is manually entered and is calculated by the postal - example - postal codes between 2000 and 2299 are Sofia. Question is how to make the formula work even if there are letters in the postal s?

Thats the formula:

=SUMPRODUCT(
    ($B$3:$CW$3)
    *(
        IF(
            ISBLANK($B$2:$CW$2);
            0;
            IFERROR(
                REPLACE(
                    $B$2:$CW$2;
                    5;
                    2;
                    ""
                );
                $B$2:$CW$2
            )
        )>=2000
        *(
            IF(
                ISBLANK($B$2:$CW$2);
                0;
                IFERROR(
                    REPLACE(
                        $B$2:$CW$2;
                        5;
                        2;
                        ""
                    );
                    $B$2:$CW$2
                )<=2299
            )
        )
    ) 
)

What it should do - based on the column with postal codes sum the revenue, but filtered for postal codes between 2000 and 2299.

What it does - calculates the revenue for all postal codes, doesnt take into account the >= 2000 and <= 2299.

Example table:

HELPERLESS VERSION

=LET( p; $B$2:$CW$2;
      sr; $B$3:$CW$3;
       f; NUMBERVALUE(IF(ISBLANK(p);1000;IFERROR(REPLACE(p;5;2;"");p)));
       x; SUM($B$3:$CW$3*(f>=2000)*(f<=2299)) )

where p are the post codes and sr are the sales revenue values.

CodePudding user response：

I found alternative solution, so ill leave it here:

In the first part i wrote the formula for the columns, so i check if there are blanks or strings in the column names and replace them, and convert to number.

First formula:

=NUMBERVALUE(IF(ISBLANK($B$2:$CW$2);1000;IFERROR(REPLACE($B$2:$CW$2;5;2;"");$B$2:$CW$2)))

Afterwards i do the calculation based on the result of the first formula:

=SUMIFS($B$3:$CW$3;$B$4:$CW$4;">=2000";$B$4:$CW$4;"<=2299")