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.

 

[Tom.Jones]

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)))

I tested your formula and the result is 1
Ditto for the formula in post no 7

 

[Peter_SSs]

I tested your formula and the result is 1
Ditto for the formula in post no 7

That would be the expected outcome.

 

[Kerryx]

If you want numerics to include 0 just change filter to >=0 as below at end of Fluff's formula

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

 

[Fluff]

Or just use the formula in post#2 which is what was originally requested.

 

[Kerryx]

That would work too

 

[Tom.Jones]

That would be the expected outcome.

I'm sorry, I didn't read the whole text, I thought it was the answer to how many numbers there are ... I'm sorry!

 

[Rick Rothstein]

Amazing. thank you so much.

The formula Fluff posted in Message #6 works for you??? You said in Message #5 that you did not have the LET function available to you in the version of Excel you would need to use the formula in. If you don't have the LET function, I would think that you do not have the FILTER function either (which Fluff's code uses). Anyway, here are a couple of alternate formulas that you can consider. Note that these formulas assume that the cells with commas do not have any text in them, only numbers.

For original question (0 considered a number)...

=SUMPRODUCT(ISNUMBER(-LEFT(A2:A10))*(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,",",""))+1))

For revised question (0 is not to be counted)...

=SUMPRODUCT(ISNUMBER(-LEFT(A2:A10))*(LEN(TRIM(SUBSTITUTE(SUBSTITUTE(A2:A10,","," ")&" "," 0 "," ")))-LEN(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A10,","," ")&" "," 0 "," ")," ","")))+1))

 

[Peter_SSs]

If you don't have the LET function, I would think that you do not have the FILTER function either

FILTER has always been available in 365 whereas LET has not. Given those posts were made a year and a half ago I suspect it was when the OP had 365, & therefore FILTER, but before they received LET.