**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have many cells where value is seperated by commas e.g- 1,5,7,8,9
3,0,4,7,8,9,52,
Just want to count total value, in the above case it should be 12

Solved by M. J. in 20 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
11/07/2018 - 01:30

Greetings! Thank you for choosing Got It Pro!

Excelchat Expert
11/07/2018 - 01:30

You need help with a possible formula, correct?

User
11/07/2018 - 01:30

Thnks nd Gd Mrng

User
11/07/2018 - 01:30

yes

Excelchat Expert
11/07/2018 - 01:31

Alright, your question is straightforward. I will help you with a solution. Please check the workbook we have here.

Excelchat Expert
11/07/2018 - 01:32

Please see cell M1

User
11/07/2018 - 01:32

But my values are not on a single cell but on lots of cells

User
11/07/2018 - 01:32

ok

Excelchat Expert
11/07/2018 - 01:33

Yes, so what we did is split it into different cells. And at the end, we have used the COUNT function.

User
11/07/2018 - 01:33

ok

Excelchat Expert
11/07/2018 - 01:33

Does this answer your question?

User
11/07/2018 - 01:35

No, but my data are in single cell with comma I can split this data and in this way I have many cells which is full of this type of data seperated by comma, Is there any way to count total number in one go

User
11/07/2018 - 01:35

I cannot split my data

Excelchat Expert
11/07/2018 - 01:36

Alright, understood. Hold on. GIve me a few minutes.

User
11/07/2018 - 01:36

ok sure

Excelchat Expert
11/07/2018 - 01:37

Here you go. Check cell B3 please

User
11/07/2018 - 01:37

ok

Excelchat Expert
11/07/2018 - 01:37

You just have to make sure that your data is separated by commas

User
11/07/2018 - 01:38

ok

Excelchat Expert
11/07/2018 - 01:38

Does this answer your question?

User
11/07/2018 - 01:38

whats the formula

Excelchat Expert
11/07/2018 - 01:38

=LEN(A4)-LEN(SUBSTITUTE(A4,",",""))+1

Excelchat Expert
11/07/2018 - 01:39

Where in A4 is the cell where your data is.

User
11/07/2018 - 01:40

in excel sheet i have written something as a whole how to count it

Excelchat Expert
11/07/2018 - 01:40

Just copy and paste the formula.

User
11/07/2018 - 01:40

answer should be 15

Excelchat Expert
11/07/2018 - 01:40

Why is it 15?

User
11/07/2018 - 01:40

total number

Excelchat Expert
11/07/2018 - 01:40

Are you referring to A7 and A8?

User
11/07/2018 - 01:41

yes

User
11/07/2018 - 01:41

yes

User
11/07/2018 - 01:41

a7 to a9

Excelchat Expert
11/07/2018 - 01:41

The count is 5 and 6.

Excelchat Expert
11/07/2018 - 01:41

Or are you saying, the sum?

Excelchat Expert
11/07/2018 - 01:41

1+2+3+4+5 = 15?

User
11/07/2018 - 01:41

in one single formula how to count

User
11/07/2018 - 01:41

Thats what i am looking for

Excelchat Expert
11/07/2018 - 01:41

You said count, not sum.

User
11/07/2018 - 01:42

yes right

Excelchat Expert
11/07/2018 - 01:42

This 1,5,7,8,9,3,0,4,7,8,9,52 should be 12?

Excelchat Expert
11/07/2018 - 01:42

Yes, then we have solved that.

User
11/07/2018 - 01:42

ya

Excelchat Expert
11/07/2018 - 01:42

I'm telling you this because per company policy, we are only allowed to answer one question per session.

User
11/07/2018 - 01:42

ok thanks

Excelchat Expert
11/07/2018 - 01:42

In this case, I will mark this as answered.

User
11/07/2018 - 01:43

ya sure

User
11/07/2018 - 01:44

ok have a nice day

Excelchat Expert
11/07/2018 - 01:45

Thank you for choosing Got It Pro! Please start a new session if you have more questions!

Excelchat Expert
11/07/2018 - 01:45

Have a great day!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*