Count non zero numbers in excel

Question

I have a string in one cell 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,2,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,2,1,0,2,5,4,0,5,4,7,6,7,8,0,8,9,8,8,10,11,0,9,10,9,8,10,9,0,15,18,17,16,17,16,0,17,16,17,16,15,14,0,12,11,10,9,10,9,0,7,8,7,6,5

In this I want to count all non zero characters but when I'm using Len and substitute function to do the same, I'm not getting 50.

I need a formula which will not count the zero, space, comma and will count 1 digit numbers as 1, 2 digit numbers as 1 and so on.

I place the string in `A1` and then count the non-zeroes `SUM(N(--TEXTSPLIT(A1,,",")<>0))` and got 50 — Rosetta, Aug 25 '23 at 05:46
Looks like Textsplit is not supported in my Excel version (2019) — silbia, Aug 25 '23 at 13:08
@silbia use `FILTERXML()` --> `=SUMPRODUCT(N(FILTERXML(""&SUBSTITUTE(A1,",","")&"","//b")<>0))` — Mayukh Bhattacharya, Aug 25 '23 at 13:38

score 4 · Answer 1 · edited Aug 25 '23 at 14:13

Use FILTERXML( )

• Formula used in cell L5

=SUMPRODUCT(N(
  FILTERXML("<m><b>"&SUBSTITUTE(L3,",","</b><b>")&"</b></m>","//b")<>0)
  )

To know more on FILTERXML( ) please take a look on this reference created by JvdV Sir. A complete encyclopedia on the above function and its usages.

Excel - Extract substring(s) from string using FILTERXML

Caveat: Since you are using Excel 2019 make sure to hit CTRL+SHIFT+ENTER while exiting the edit mode.

However, with MS365 I would have used TEXTSPLIT( ) as mentioned above by Rosetta wrapped within a SUM( ) and N( ) to coerce the BOOLEANS to 1 and 0s respectively.

• Formula used in cell L5

=SUM(N(TEXTSPLIT(L3,",")<>"0"))

Or, More cleaner and less verbose way as mentioned in comments by JvdV Sir

• Formula used in cell L5

=ROWS(FILTERXML("<m><b>"&SUBSTITUTE(L3,",","</b><b>")&"</b></m>","//b[.!=0]"))

Nice use of `FILTERXML()`. You can also directly filter the nodes using xpath: `=ROWS(FILTERXML(""&SUBSTITUTE(L3,",","")&"","//b[.!=0]"))` — JvdV, Aug 25 '23 at 14:09
Alright @JvdV Sir, understood thank you very much as always. — Mayukh Bhattacharya, Aug 25 '23 at 14:10

score 3 · Answer 2 · answered Aug 25 '23 at 14:15

3

For Excel -2019, I believe that the following might work:

Formula in B1:

=LEN(SUBSTITUTE(","&A1,",0",))-LEN(SUBSTITUTE(SUBSTITUTE(","&A1,",0",),",",))

answered Aug 25 '23 at 14:15

JvdV

70,606
8
39
70

1

More simpler way, sir your presence of mind is always great. – Mayukh Bhattacharya Aug 25 '23 at 14:18
1

Some of the stuff around I see makes nice use of new dynamic functions though. – JvdV Aug 25 '23 at 14:19
Absolutely sir agreed! – Mayukh Bhattacharya Aug 25 '23 at 14:20
2

Here's a slightly obscure variant which saves one substitution =LEN(","&A1)/2+LEN(SUBSTITUTE(","&A1,",0",))/2-LEN(SUBSTITUTE(A1,",",)) – Tom Sharpe Aug 26 '23 at 07:48
1

@TomSharpe thanks. could just be good enough for OP here. Nice! – JvdV Aug 26 '23 at 08:49

score 2 · Answer 3 · answered Aug 25 '23 at 06:37

Count Non-Zeros in Delimited String

Single Cell

=LET(data,A1,
    d,--TEXTSPLIT(data,,","),
    f,FILTER(d,ISNUMBER(d)),
COUNT(FILTER(f,f<>0)))

Range

=LET(data,A1:C4,
MAP(data,LAMBDA(r,LET(
    d,--TEXTSPLIT(r,,","),
    f,FILTER(d,ISNUMBER(d)),
COUNT(FILTER(f,f<>0))))))

Variables

d - split and converted to numbers (in a column)
f - only numbers

score 0 · Answer 4 · answered Aug 25 '23 at 05:59

0

This should do it, see picture for details:

=LET(
    t, C3,
    r, C1,
    clean, REDUCE(
        t,
        MID(r, SEQUENCE(LEN(r)), 1),
        LAMBDA(u,v, TRIM(SUBSTITUTE(u, v, " ")))
    ),
    COUNTA(TEXTSPLIT(clean, " "))
)

answered Aug 25 '23 at 05:59

Cary Ballard

111
6

Ritesh Hirapara · Answer 5 · 2023-08-26T03:25:00.847

-1

You can use this formula for count non-zero number in cell.

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)<>","), --(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)<>"0"))

It works for me I hope you too. if doesn't work let me know your software version.

edited Aug 26 '23 at 03:25

answered Aug 25 '23 at 06:06

Ritesh Hirapara

16
4

The string is in one cell, so why do you have a range? – Solar Mike Aug 25 '23 at 13:19
okay so you can use below =SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)<>","), --(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)<>"0")) – Ritesh Hirapara Aug 26 '23 at 03:24
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 30 '23 at 19:18

Count non zero numbers in excel

5 Answers5

Count Non-Zeros in Delimited String