Sum comma separated values in Excel - Excel Tips and Tricks

Опубликовано: 14 Январь 2025
на канале: Rabi Gurung

6,205

286

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?

By using this formula.

=SUM(--(TEXTSPLIT(B2,",")))

Here's how the formula works:

TEXTSPLIT(B2,","): This function splits the text in cell B2 into separate values based on the comma delimiter. For example, if cell B2 contains the text "10,20,30", the TEXTSPLIT function will return an array of three values: {"10","20","30"}.

--(TEXTSPLIT(B2,",")): The double unary operator ("--") is used to convert the text values in the array to numeric values. If the values in the array cannot be converted to numbers, the result will be an error value. In our example, the result of this part of the formula would be an array of numeric values: {10,20,30}.

SUM(--(TEXTSPLIT(B2,","))): The SUM function then adds up the numeric values in the array and returns the total sum. In our example, the result would be 60 (i.e., the sum of 10, 20, and 30).

🔗🔗 LINKS TO SIMILIAR VIDEOS 🔗🔗
Sum comma separated values in Excel - Excel Tips and Tricks
   • Sum comma separated values in Excel -...

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

Separate data from one cell in Excel with commas - Excel Tip and Tricks
   • Separate data from one cell in Excel ...

Text Split with multiple delimiters - Excel Tip and Tricks
   • Text Split with multiple delimiters -...

[NO FORMULA] Separate data from one cell in Excel with commas - Excel Tip and Tricks
   • [NO FORMULA] Separate data from one c...

Sum comma separated values in jaggered format in Excel - Excel Tips and Tricks
   • Sum comma separated values in jaggere...

Get maximum of comma-separated values in a cell In Excel - Excel Tips and Tricks
   • Get maximum of comma-separated values...

Sum comma separated values in Google Sheets - Excel Tips and Tricks
   • Sum comma separated values in Google ...

#microsoft #excel #exceltips #tips #exceltricks #tricksandtips