SOLVED

Sum values if cell contains text that isin my source cell (Vlookup cell contains a smultiple values)

%3CLINGO-SUB%20id%3D%22lingo-sub-3295807%22%20slang%3D%22en-US%22%3ESum%20values%20if%20cell%20contains%20text%20that%20isin%20my%20source%20cell%20(Vlookup%20cell%20contains%20a%20smultiple%20values)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3295807%22%20slang%3D%22en-US%22%3E%3CP%3EGreetings%3C%2FP%3E%3CP%3ECan%20someone%20please%20help%20me%20with%20the%20following%20simplified%20example.%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20containing%3C%2FP%3E%3CP%3ECode%26nbsp%3B%20Amount%3C%2FP%3E%3CP%3E101%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2410%3C%2FP%3E%3CP%3E101%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2420%3C%2FP%3E%3CP%3E102%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2410%3C%2FP%3E%3CP%3E103%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2420%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20a%20formulae%20to%20sum%20If%20the%20as%20follows%3C%2FP%3E%3CP%3E101%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%2430%3C%2FP%3E%3CP%3E101%2C103%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%2450%3C%2FP%3E%3CP%3E101%2C102%2C103%26nbsp%3B%20%26nbsp%3B%2460%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3295807%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3297332%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20values%20if%20cell%20contains%20text%20that%20isin%20my%20source%20cell%20(Vlookup%20cell%20contains%20a%20smultiple%20val%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3297332%22%20slang%3D%22en-US%22%3EGlad%20I%20could%20help%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1373181%22%20target%3D%22_blank%22%3E%40Anthonyinnz%3C%2FA%3E%20%26amp%3B%20Thanks%20for%20posting%20back%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3297241%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20values%20if%20cell%20contains%20text%20that%20isin%20my%20source%20cell%20(Vlookup%20cell%20contains%20a%20smultiple%20val%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3297241%22%20slang%3D%22en-US%22%3EExcellent%20-%20Thank%20You%3CBR%20%2F%3EI%20tried%20using%20several%20different%20functions%20without%20success%20...%20didnt%20think%20of%20the%20above%20%3CLI-EMOJI%20id%3D%22lia_monkey-face%22%20title%3D%22%3Amonkey_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3296021%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20values%20if%20cell%20contains%20text%20that%20isin%20my%20source%20cell%20(Vlookup%20cell%20contains%20a%20smultiple%20val%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3296021%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1373181%22%20target%3D%22_blank%22%3E%40Anthonyinnz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20387px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F367174iB85CB5DDB8E51B9D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3EE3%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSUM(%20ISNUMBER(SEARCH(Table1%5BCode%5D%2C%5B%40Codes%5D))%20*%20Table1%5BAmount%5D%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSTRONG%3EAND%3C%2FSTRONG%3E%20validate%20it%20with%20%3CSTRONG%3E%5BCtrl%5D%2B%5BShift%5D%2B%5BEnter%5D%3C%2FSTRONG%3E%20(or%20equiv.%20on%20Mac)%20if%20you%20don't%20run%20Excel%202021%20or%20365%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Greetings

Can someone please help me with the following simplified example.

I have a spreadsheet containing

Code  Amount

101     $10

101     $20

102     $10

103     $20

 

I want a formulae to sum If the as follows

101                $30

101,103          $50

101,102,103   $60

3 Replies
best response confirmed by Anthonyinnz (New Contributor)
Solution

Hi @Anthonyinnz 

 

_Screenshot.png

 

in E3:

=SUM( ISNUMBER(SEARCH(Table1[Code],[@Codes])) * Table1[Amount] )

AND validate it with [Ctrl]+[Shift]+[Enter] (or equiv. on Mac) if you don't run Excel 2021 or 365

Excellent - Thank You
I tried using several different functions without success ... didnt think of the above