Sum comma separated values in Excel Without Using TEXTSPLIT() Function - Excel Tips and Tricks - video Dailymotion

ExcelTips247

Discover how to sum comma separated values in Excel. Essentially, the answer to how to sum numbers with commas in a single Excel cell. Or how to sum numbers with commas in a single Excel cell?   =SUM(IF(ISNUMBER(VALUE(TRIM(    MID(SUBSTITUTE(B2, ",",     REPT(" ", LEN(B2))),      (ROW(INDIRECT("1:" &LEN(B2)))      - 1) * LEN(B2) + 1, LEN(B2))))),      VALUE(TRIM(MID(SUBSTITUTE(B2, ",",      REPT(" ", LEN(B2))), (ROW(INDIRECT(      "1:" & LEN(B2))) - 1) * LEN(B2) + 1,      LEN(B2)))), 0))    Let's break this formula down step by step.   =SUM(IF(ISNUMBER(VALUE(TRIM(MID(SUBSTITUTE(B2, ",", REPT(" ", LEN(B2))), (ROW(INDIRECT("1:" & LEN(B2))) - 1) * LEN(B2) + 1, LEN(B2))))), VALUE(TRIM(MID(SUBSTITUTE(B2, ",", REPT(" ", LEN(B2))), (ROW(INDIRECT("1:" & LEN(B2))) - 1) * LEN(B2) + 1, LEN(B2)))), 0))    🔗🔗 LINKS TO SIMILIAR VIDEOS 🔗🔗  Sum comma separated values in Excel - Excel Tips and Tricks  https://youtube.com/shorts/1GUx7zi2wzc?si=Rc8Oidvtr6t-dOFV   Sum comma separated values in Excel Without Using TEXTSPLIT() Function - Excel Tips and Tricks  https://youtube.com/shorts/z6ghCP7G3ew?si=rIS6nZL31jA41g17   Separate data from one cell in Excel with commas - Excel Tip and Tricks  https://youtube.com/shorts/xuhpFwb5TWg?si=8YYj22Elb2Ez2rMp   Text Split with multiple delimiters - Excel Tip and Tricks  https://youtube.com/shorts/LXZkMlGZWXQ?si=v-ovLGoZ2SdQ-baC   [NO FORMULA] Separate data from one cell in Excel with commas - Excel Tip and Tricks  https://youtube.com/shorts/4AhokAuE5Nc?si=ObQffk0YBaj0SgyU   Sum comma separated values in jaggered format in Excel - Excel Tips and Tricks  https://youtube.com/shorts/ijC7qhRK6s0?feature=share   Get maximum of comma-separated values in a cell In Excel - Excel Tips and Tricks  https://youtube.com/shorts/UHdjAsSx6C8?feature=share   Sum comma separated values in Google Sheets - Excel Tips and Tricks  https://youtube.com/shorts/bYOX6V_iWnI?feature=share     double unary operator, How to sum numbers with commas in a single Excel cell?, How to Sum Numbers With Commas in a Single Excel Cell?,sum numbers separated by commas online,excel index match comma separated values,how to put comma in numbers in excel formula,how to add multiple numbers in one cell excel,how to format numbers in excel with commas,countif comma separated values,how to put comma after 3 digits in excel,excel average comma separated values,

Transcript

00:00I recently uploaded a video on how to sum a series of number in a cell which is comma

00:04separated using textSplate function, however, someone had enquired how would I do it without

00:10textSplate function itself.

00:12This is how you can do it.

00:14Just a word of cautious, the formula is hairy.

00:16In a nutshell, this formula breaks down a text cell in B2 into comma-separated substring

00:22and then converts each valid substring to a numeric value and then sums all the numeric

00:28value.

00:29If you want a copy of this formula, please leave a comment in the comment section requesting

00:38for this formula and I'll send the formula your way.

00:42Once that's done, hit enter and after that you can apply the formula to the rest of the

00:46row by double-clicking on the fill handler like this.

Sum comma separated values in Excel Without Using TEXTSPLIT() Function - Excel Tips and Tricks

Category

Transcript

Comments

Recommended