Sum a cell that contains multiple numbers that represent other values

[NateTiberius]

Hey.

I have hundreds of cells that each contain different number codes that represent another value. For instance, a cell might contain, "4, 16, 32". Each of those distinct numbers correlates to other values. For instance, "4" equals 3.58, 16 equals 2.67, and 32 equals 2.75, etc. What I need is a formula that will read those number codes in the cell and then sum the numbers they represent. So, in the case of the "4, 16, 32" cell, it would sum to 9.00.

So, using the fourth formula listed here – How to use Excel formula: "If cell contains" | SoftwareKeep – I was able to ALMOST make this happen. Below is an example formula:

=IF(OR(ISNUMBER(SEARCH("4",H2))),F5)+IF(OR(ISNUMBER(SEARCH("16",H2))),F17)+IF(OR(ISNUMBER(SEARCH("32",H2))),F33)

Using that formula gets me my number I want for the above example, but there's a problem. There are codes that range from 1 through 38. So one cell might only have a "1", another only a "5", and another a "15". However, using that formula, when it reads a "15", it sees it as not only a "15", but as a "1", a "5", AND a "15". So it adds three numbers together instead of only the 15.

I thought I could "outsmart" that by instead having the formula search for "1," (with a comma). But that does not work because certain cells END in single digit number (or, as mentioned above, ONLY contain a single number): for instance, "15, 6, 1". So using the "1," method, causes the formula not to find that cell-ending "1". Below is the full formula I was using to add all the codes from 1 through 38 (I purposefully left out a couple numbers).

=IF(OR(ISNUMBER(SEARCH("1,",'33122 Raw'!C2))),Key!F2)+IF(OR(ISNUMBER(SEARCH("2,",'33122 Raw'!C2))),Key!F3)+IF(OR(ISNUMBER(SEARCH("3,",'33122 Raw'!C2))),Key!F4)+IF(OR(ISNUMBER(SEARCH("4,",'33122 Raw'!C2))),Key!F5)+IF(OR(ISNUMBER(SEARCH("5,",'33122 Raw'!C2))),Key!F6)+IF(OR(ISNUMBER(SEARCH("6,",'33122 Raw'!C2))),Key!F7)+IF(OR(ISNUMBER(SEARCH("8,",'33122 Raw'!C2))),Key!F9)+IF(OR(ISNUMBER(SEARCH("9,",'33122 Raw'!C2))),Key!F10)+IF(OR(ISNUMBER(SEARCH("10",'33122 Raw'!C2))),Key!F11)+IF(OR(ISNUMBER(SEARCH("11",'33122 Raw'!C2))),Key!F12)+IF(OR(ISNUMBER(SEARCH("12",'33122 Raw'!C2))),Key!F13)+IF(OR(ISNUMBER(SEARCH("13",'33122 Raw'!C2))),Key!F14)+IF(OR(ISNUMBER(SEARCH("14",'33122 Raw'!C2))),Key!F15)+IF(OR(ISNUMBER(SEARCH("15",'33122 Raw'!C2))),Key!F16)+IF(OR(ISNUMBER(SEARCH("16",'33122 Raw'!C2))),Key!F17)+IF(OR(ISNUMBER(SEARCH("17",'33122 Raw'!C2))),Key!F18)+IF(OR(ISNUMBER(SEARCH("18",'33122 Raw'!C2))),Key!F19)+IF(OR(ISNUMBER(SEARCH("19",'33122 Raw'!C2))),Key!F20)+IF(OR(ISNUMBER(SEARCH("20",'33122 Raw'!C2))),Key!F21)+IF(OR(ISNUMBER(SEARCH("21",'33122 Raw'!C2))),Key!F22)+IF(OR(ISNUMBER(SEARCH("22",'33122 Raw'!C2))),Key!F23)+IF(OR(ISNUMBER(SEARCH("23",'33122 Raw'!C2))),Key!F24)+IF(OR(ISNUMBER(SEARCH("27",'33122 Raw'!C2))),Key!F28)+IF(OR(ISNUMBER(SEARCH("28",'33122 Raw'!C2))),Key!F29)+IF(OR(ISNUMBER(SEARCH("30",'33122 Raw'!C2))),Key!F31)+IF(OR(ISNUMBER(SEARCH("30",'33122 Raw'!C2))),Key!F31)+IF(OR(ISNUMBER(SEARCH("31",'33122 Raw'!C2))),Key!F32)+IF(OR(ISNUMBER(SEARCH("32",'33122 Raw'!C2))),Key!F33)+IF(OR(ISNUMBER(SEARCH("33",'33122 Raw'!C2))),Key!F34)+IF(OR(ISNUMBER(SEARCH("34",'33122 Raw'!C2))),Key!F35)+IF(OR(ISNUMBER(SEARCH("35",'33122 Raw'!C2))),Key!F36)+IF(OR(ISNUMBER(SEARCH("36",'33122 Raw'!C2))),Key!F37)+IF(OR(ISNUMBER(SEARCH("38",'33122 Raw'!C2))),Key!F39)

Long story short, does anyone have a formula to accomplish this goal?

 

[Fluff]

That formula should not need Ctrl Shift Enter. Do you have the UNIQUE function?

 

[NateTiberius]

That formula should not need Ctrl Shift Enter. Do you have the UNIQUE function?

No.

 

[Fluff]

In that case your Excel is about 4years out of date.
Try this instead

=SUMPRODUCT(SUMIFS($B:$B,$A:$A,FILTERXML("<k><m>"&SUBSTITUTE(E2,",","</m><m>")&"</m></k>","//m")))

 

[NateTiberius]

In that case your Excel is about 4years out of date.
Try this instead

=SUMPRODUCT(SUMIFS($B:$B,$A:$A,FILTERXML("<k><m>"&SUBSTITUTE(E2,",","</m><m>")&"</m></k>","//m")))

You are correct. We don't use Excel much where I work, so it turns out we have the archaic Office 2019. I updated my profile accordingly. Also, that updated formula worked!

 

[Fluff]

Glad to help & thanks for the feedback.