MS Excel/O365 - Count comma-separated numbers in range

[elmerg]

Hello everyone. Need some help with an Excel counting function; I figured it would be simple, but apparently it's not.

I need a total count of the numeric values in a column; some of the cells will have comma-separated entries, some will have single numerical entries, and some will have words.

For an example of data:

-
No data
85, 93
100
85, 86
No data
No data
100
92, 0
95

I need to count ONLY the numbers, including any zeroes, leaving out anything that is non-numeric. Some numbers may be repeated within the same cells. I also cannot separate out the numbers due to data organization needs.

So if I was counting the above example, my count output would be 9.

Any assistance would be appreciated.

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have textjoin maybe (dependant on version this may need array entry)

+Fluff 1.xlsm
	A	B	C
14	-		9
15	No data
16	85, 93
17	100
18	85, 86
19
20	No data
21	100
22	92, 0
23	95
24
Main

Cell Formulas
Range	Formula
C14	C14	=COUNT(FILTERXML("<o><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(A14:A23,",","</m><m>"))&"</m></o>","//m"))

 

[elmerg]

Thank you, I will do the update to my profile.

And thank you again, that worked perfectly for what I needed..

Would you also have thoughts on how to alter that to count only the single zeroes?

So if counting the above example, we would just have a result of 1.

 

[Fluff]

If you have the new LET function try

=LET(xml,FILTERXML("<o><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(A14:A23,",","</m><m>"))&"</m></o>","//m"),COUNT(FILTER(xml,xml=0)))

 

[elmerg]

If you have the new LET function try

=LET(xml,FILTERXML("<o><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(A14:A23,",","</m><m>"))&"</m></o>","//m"),COUNT(FILTER(xml,xml=0)))

Thank you for the input. I do not have the let() function in the version of Excel I have access to for this project.

 

[Fluff]

Ok, not a problem, just means a slightly longer formula

=COUNT(FILTER(FILTERXML("<o><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(A14:A23,",","</m><m>"))&"</m></o>","//m"),FILTERXML("<o><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(A14:A23,",","</m><m>"))&"</m></o>","//m")=0))

 

[elmerg]

Ok, not a problem, just means a slightly longer formula

=COUNT(FILTER(FILTERXML("<o><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(A14:A23,",","</m><m>"))&"</m></o>","//m"),FILTERXML("<o><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(A14:A23,",","</m><m>"))&"</m></o>","//m")=0))

Amazing. thank you so much. I'm working from a 'light to moderate use' Excel need, so something like this is a bit beyond my current knowledge for it. So I appreciate the help. I will also dig into these new functions you've used here, so I can better understand how they work in the future. Many thanks!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[thedoccontroller]

You're welcome & thanks for the feedback.

@Fluff ,
Can you create an exception to the original formula you made so that it will not count "0"? Thanks in advance.

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Just change the =0 to <>0