Excel Pro Trick: Sum Comma Separated Values in Excel with Formula using TEXTSPLIT + LAMBDA Function

Learn how to sum comma separated values or numbers separated by comma in a single cell in Excel using a simple to understand Excel formula.

In this Excel tutorial we learn how to use TEXTSPLIT function to sum comma separated values or numbers that are separated by a delimiter like comma, colon, semicolon etc. using Excel formula.

In addition to SUM and TEXTSPLIT function, we will also explore how to use MAP and LAMBDA functions to add comma separated values using array formulas without the need to drag the fill handle. LAMBDA function will automatically spill the totals for us in the column.

Lets Excel!

Access or download practice workbook file for this Excel tutorial:

For more Excel tips, tricks and tutorials don't forget to subscribe to @LearnExceltoexcel channel.

Learn #Excel your way:
===================
--------------------------------------

Pick your favourite social page:

#excelsum #excel #sumif #conditionalsum #conditionalsumexcel #exceltutorial #exceltips #excelformula #excelformulas #excelformulasandfunctions #excelformulaforjobinterview #excelformulatypes #excelsum #excelmap #lambda #excellambda #exceltextsplit

[Jamalhappy]

Zabardast.... Yeh buhat kaam ka formula hai

[LearnExceltoexcel]

The ones without Excel 365 can use this formula instead:
=SUM(FILTERXML("<t><s>"&SUBSTITUTE(C4, ", ", "</s><s>")&"</s></t>", "//s"))

Update: Practice file included with all three methods i.e. TEXTSPLIT/ LAMBDA / FILTERXML

[normaltirpoe]

Sorting data/input correctly would show you who has the best sales per specific month an might be input to area focused specific sales .(Big Data )

[kranthinagella]

I can use find and replace and do this without using complex formulae

[soundaryasuresh]

Bro explanation pls for filterxml formula

[ShashankKhandelwal-yo]

Why and What is the use of two minus sign in this formula

[Parachute]

Hello Broh.. will this command work for Excel2016 ?

[bossssss]

Rule number 1. DONT EVER organise your data like that and it would mean, less of the need to retained complex formula.

[RicoASHW]

i can't find the function (--(TEXTSPLIT)) on spreadsheet and microsoft excel 2019. What excel do you use?

[jeriheriyanto]

Why this formula. Not work at excel 2021

[omprakashthakur]

Hello sir without textsplit how to solve problems please sir

[wolft]

doesn't work with my excel 365 mac