Excel value calculation based on column name

Question

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: https://wetransfer.com/downloads/18192739faa71754a449a0198cef949120220530083432/b964ef1f10d6b22d2bfcf2e30903811b20220530083447/6ef71f

An upvote for the Excel formula: this is the clearest way I've ever seen an Excel formula being written. — Dominique, May 30 '22 at 08:44

mark fitzpatrick · Accepted Answer · 2022-05-31T11:11:44.547

1

Perhaps :

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

(sorry for the reformat)

or a more simplified version:

=SUM( $B$3:$CW$3
      * ( LEFT( $B$2:$CW$2, 4 ) >= "2000" )
      * ( LEFT( $B$2:$CW$2, 4 ) <= "2299" )
     )

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)));
       SUM($B$3:$CW$3*(f>=2000)*(f<=2299)) )

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

edited May 31 '22 at 11:11

answered May 30 '22 at 10:46

mark fitzpatrick

3,162
2
11
23

Maybe it has something to do with my formating, but the formulas didnt work for me, i posted another solution. Thank you for your response, appreciated! – Stuxnet May 31 '22 at 05:34
I just realized as I read your solution that I forgot to convert back from comma delimited functions to semicolon delimited. That might be the issue, but I am not sure. In any case, I have never used NUMBERVALUE: I have always used VALUE. NUMBERVALUE is quite useful - good to know! – mark fitzpatrick May 31 '22 at 05:36
I converted yours to semicolon delimited. :) – Stuxnet May 31 '22 at 05:44
This is so strange. I put your formula in and it works. Then I decided modify it to give a version of your approach that does not use helper cells. It generates an array of VALUE errors. So I modified yours by replacing the SUMIFS with SUM and it works. I think this could have something to do with the way that our installations evaluate text. I will add the new (helper-less) version to my answer if it helps. – mark fitzpatrick May 31 '22 at 06:06
1

yeah that could be the case, thank you for your time! – Stuxnet May 31 '22 at 07:02

score 1 · Answer 2 · answered May 31 '22 at 05:32

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

Excel value calculation based on column name

2 Answers2

HELPERLESS VERSION