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.
