CODEWITHSUNDEEP
excelexcel-formulaexcel-2003
Zoke
Zoke

Reputation: 43

Excel: complicated SUMPRODUCT formula needed

I'm in search of a SUMPRODUCT formula, or a similar sort of formula which does the same thing. It should do the following:

  1. On worksheet A it needs to ignore incorrect zipcodes, meaning zipcodes which do not consist of 4 numbers and 2 letters need to be ignored. It also needs to take into account that there sometimes are superfluous spaces behind the zipcode. And sometimes there is a space between the numbers and letters, sometimes there isn't. Just the 4 numbers and 2 letters need to be compared.
  2. The correct zipcodes on worksheet A need to be compared with the zipcodes on worksheet B. If they match, then all the values behind the zip code need to be summed up. If there is another record starting with the same zipcode then these need to be added up as well.
  3. Neither of the worksheets should need to be changed, since the data is generated frequently. The formula should be able to work on a third, separate worksheet. And it should work in Excel 2003.

EDIT: Added point 3.

I'll add an image to visualize what I mean. Hopefully someone can help me!

enter image description here

Upvotes: 1

Views: 282

Answers (2)

Jerry
Jerry

Reputation: 71578

With some helper columns, you could use something like this (open in new tab for larger version):

enter image description here

The formulae:

In B2 to remove spaces and hence get a 'clean' ZIP and check the length:

=IF(LEN(SUBSTITUTE(A2," ",""))=6,SUBSTITUTE(A2," ",""),"")

In C2, to get the sum:

=IFERROR(IF(AND(ISNUMBER(LEFT(B2,4)*1),CODE(MID(LOWER(B2),5,1))>=97,CODE(MID(LOWER(B2),5,1))<=122,CODE(RIGHT(LOWER(B2)))>=97,CODE(RIGHT(LOWER(B2)))<=122),SUMPRODUCT($H$2:$K$8*($G$2:$G$8=B2)),""),"")

In G2, I used the same one as in B2:

=IF(LEN(SUBSTITUTE(F2," ",""))=6,SUBSTITUTE(F2," ",""),"")

Without the helper, the formula becomes much longer because of repeating parts:

=IFERROR(IF(AND(LEN(SUBSTITUTE(A2," ",""))=6,ISNUMBER(LEFT(SUBSTITUTE(A2," ",""),4)*1),CODE(MID(LOWER(SUBSTITUTE(A2," ","")),5,1))>=97,CODE(MID(LOWER(SUBSTITUTE(A2," ","")),5,1))<=122,CODE(RIGHT(LOWER(SUBSTITUTE(A2," ",""))))>=97,CODE(RIGHT(LOWER(SUBSTITUTE(A2," ",""))))<=122),SUMPRODUCT($H$2:$K$8*(SUBSTITUTE($F$2:$F$8," ","")=SUBSTITUTE(A2," ",""))),""),"")

Or

=IFERROR(
    IF(
        AND(
            LEN(SUBSTITUTE(A2," ",""))=6,                      ' Check length
            ISNUMBER(LEFT(SUBSTITUTE(A2," ",""),4)*1),         ' Check numbers
            CODE(MID(LOWER(SUBSTITUTE(A2," ","")),5,1))>=97,   ' Check if letter
            CODE(MID(LOWER(SUBSTITUTE(A2," ","")),5,1))<=122,  ' Check if letter
            CODE(RIGHT(LOWER(SUBSTITUTE(A2," ",""))))>=97,     ' Check if letter
            CODE(RIGHT(LOWER(SUBSTITUTE(A2," ",""))))<=122     ' Check if letter
        ),
        SUMPRODUCT(
            $H$2:$K$8*
            (SUBSTITUTE($F$2:$F$8," ","")=SUBSTITUTE(A2," ",""))),
        ""
    ),
    ""
)

Oops, forgot that IFERROR was not in 2003. The only reason why I used it was that MID would return an empty string and CODE would subsequently give an error. You can use the below instead which makes sure the string is 6 chars first:

=IF(LEN(SUBSTITUTE(A2," ",""))=6,IF(AND(ISNUMBER(LEFT(SUBSTITUTE(A2," ",""),4)*1),CODE(MID(LOWER(SUBSTITUTE(A2," ","")),5,1))>=97,CODE(MID(LOWER(SUBSTITUTE(A2," ","")),5,1))<=122,CODE(RIGHT(LOWER(SUBSTITUTE(A2," ",""))))>=97,CODE(RIGHT(LOWER(SUBSTITUTE(A2," ",""))))<=122),SUMPRODUCT($H$2:$K$8*(SUBSTITUTE($F$2:$F$8," ","")=SUBSTITUTE(A2," ",""))),""),"")

Upvotes: 3

Kokkie
Kokkie

Reputation: 556

Here you have a formula to validate Dutch postal codes

=AND(LEN(A2)=6; ISNUMBER(VALUE(LEFT(A2;4))); CODE(MID(LOWER(A2);5;1)) >= 97; CODE(MID(LOWER(A2);5;1)) <= 122; CODE(MID(LOWER(A2);6;1)) >= 97; CODE(MID(LOWER(A2);6;1)) <= 122)

0-9 = ASCII code 48 to 57
a-z = ASCII code 97 to 122 (lowercase)

In case you have a Dutch version of Excel, the formula would be:

=EN(LENGTE(A2)=6; ISGETAL(WAARDE(LINKS(A2;4))); CODE(DEEL(KLEINE.LETTERS(A2);5;1)) >= 97; CODE(DEEL(KLEINE.LETTERS(A2);5;1)) <= 122; CODE(DEEL(KLEINE.LETTERS(A2);6;1)) >= 97; CODE(DEEL(KLEINE.LETTERS(A2);6;1)) <= 122)

Upvotes: 1

Related Questions