Search code examples
excelexcel-formulasumproduct

How to Sumproduct in Excel if partial string is found ignoring characters and specific cell values

I have a table similar to the one below in Excel and I want to calculate the sum of all the columns where "*weightl*" shows up in the variable name (first row).

However, if the value is 8888 or a character/string this should be ignored.

enter image description here

In this case, the result should be 64.

I tried using something like {+sumproduct(sumif(A1:F1,"\*weightl\*",A2:F4))}

However, this only returns 16 (the result of the first row). Also, I don't know how to exclude 8888 values.

Would be great if you could support with a formula to solve this!

Solution

  • Welcome to SO. For this you'll need an array formula:

    =SUM(IF(IFERROR(SEARCH("weight",$A$1:$F$1),0)>0,IF($A$2:$F$4<>8888,$A$2:$F$4)))

    NOTE: Because this in an array formula, it must be inserted with CTRL+ENTER+SHIFT instead of only ENTER or it won't work!

    enter image description here

    How this works?

    1. IF($A$2:$F$4<>8888,$A$2:$F$4) will return an array of only those cells which value are not equal to 8888
    2. IF(IFERROR(SEARCH("weight",$A$1:$F$1),0)>0,IF($A$2:$F$4<>8888,$A$2:$F$4)) will check previous array of values, and it will search the word weight in row 1. If found, those values will be checked as ok. If not, those values will be ignored in the final sum.

    As you can see in the image I posted, final result is 64.

    Hope you can adapt this to your needs.