Total Count of 5 columns with different numbers in each cell acros

[Guest]

My question is simple. I would like the total count of each cell, in 5 columns in the same row. Each column has a cell with different numbers or no numbers in the cell. For example:
A B C D E F (total of A-E)
25, 26, 24 5,7 0 1, 5, 8,10 2 10

My answer should be 10.
How do I get excel to count the number of entries in each cell and give me the total of all the cells for the same row, not the sum.
Thank You...RAS

 

[Juan Sanchez]

RAS

try: (all in one line)

=SUM(IF(MID(A1:E1,ROW(INDIRECT("1:"&LEN(A1:E1))),1)
=",",1,0),IF(A1:E1<>0,1,0))

It is an array formula so commit with CTRL+SHIFT+ENTER
instead of just ENTER.

The formula will work if your values in a cell are always
separated with commas (,) so if you dont use comma it
won't count it...

Cheers
Juan

-----Original Message-----
My question is simple. I would like the total count of

each cell, in 5 columns in the same row. Each column has a
cell with different numbers or no numbers in the cell.
For example:

A B C

D E F (total of A-E)

25, 26, 24 5,7 0 1, 5, 8,10 2 10

My answer should be 10.
How do I get excel to count the number of entries in each

cell and give me the total of all the cells for the same
row, not the sum.

 

[RagDyer]

Try this array formula.
Must be entered with CSE (<Ctrl> <Shift> <Enter>), which will
*automatically* enclose the formula in curly brackets.
=SUM((--LEFT(TRIM(A1:E1))>0)*(LEN(A1:E1)-LEN(SUBSTITUTE(A1:E1,",",""))+1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



My question is simple. I would like the total count of each cell, in 5
columns in the same row. Each column has a cell with different numbers or
no numbers in the cell. For example:
A B C D E F
(total of A-E)
25, 26, 24 5,7 0 1, 5, 8,10 2 10

My answer should be 10.
How do I get excel to count the number of entries in each cell and give me
the total of all the cells for the same row, not the sum.
Thank You...RAS

 

[RagDyer]

Original didn't work if cells were empty.

Try this instead:

=SUM((A1:E1>0)*(LEN(A1:E1)-LEN(SUBSTITUTE(A1:E1,",",""))+1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Try this array formula.
Must be entered with CSE (<Ctrl> <Shift> <Enter>), which will
*automatically* enclose the formula in curly brackets.
=SUM((--LEFT(TRIM(A1:E1))>0)*(LEN(A1:E1)-LEN(SUBSTITUTE(A1:E1,",",""))+1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



My question is simple. I would like the total count of each cell, in 5
columns in the same row. Each column has a cell with different numbers or
no numbers in the cell. For example:
A B C D E F
(total of A-E)
25, 26, 24 5,7 0 1, 5, 8,10 2 10

My answer should be 10.
How do I get excel to count the number of entries in each cell and give me
the total of all the cells for the same row, not the sum.
Thank You...RAS

 

[RagDyer]

We'll get this right yet !

Formula is still an array formula.

Must be entered with CSE (<Ctrl> <Shift> <Enter>), which will
*automatically* enclose the formula in curly brackets.

=SUM((A1:E1>0)*(LEN(A1:E1)-LEN(SUBSTITUTE(A1:E1,",",""))+1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Original didn't work if cells were empty.

Try this instead:

=SUM((A1:E1>0)*(LEN(A1:E1)-LEN(SUBSTITUTE(A1:E1,",",""))+1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Try this array formula.
Must be entered with CSE (<Ctrl> <Shift> <Enter>), which will
*automatically* enclose the formula in curly brackets.
=SUM((--LEFT(TRIM(A1:E1))>0)*(LEN(A1:E1)-LEN(SUBSTITUTE(A1:E1,",",""))+1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



My question is simple. I would like the total count of each cell, in 5
columns in the same row. Each column has a cell with different numbers or
no numbers in the cell. For example:
A B C D E F
(total of A-E)
25, 26, 24 5,7 0 1, 5, 8,10 2 10

My answer should be 10.
How do I get excel to count the number of entries in each cell and give me
the total of all the cells for the same row, not the sum.
Thank You...RAS

 

[RagDyeR]

You received the error message because you *didn't* enter it as an array
formula, as I instructed, with CSE !

Must be entered with CSE (<Ctrl> <Shift> <Enter>), which will
*automatically* enclose the formula in curly brackets.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



The worksheet I am trying to create will be a template for others to enter
in. If I use this formula and the cells are all blank a get $VALUE! Is
there a formula that can be used whether the cells are filled or empty?
Thanks for all your time...RAS

 

[RagDyer]

YOU USED THE WRONG FORMULA !

Didn't you read my second post, which stated that the *original* formula
*didn't* work with empty cells, and therefore, I posted a revised formula
that *does* work with empty cells, and mus be entered using CSE.

Here it is again:
=SUM((A1:E1>0)*(LEN(A1:E1)-LEN(SUBSTITUTE(A1:E1,",",""))+1))

I repeated it in the third post just because I neglected to mention in the
second one, that is was an array formula, and must be entered with CSE.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Sorry, I did enter with CSE. I used the formula that worked with no empty
cells.
=SUM((--LEFT(TRIM(C3:G3))>0)*(LEN(C3:G3)-LEN(SUBSTITUTE(C3:G3,",",""))+1))
and entered as an array.
Formula doesn't work when all the cells are left blank, should there always
be a zero. I would rather not have to enter zero's if possible.
Again, Thanks for your time.....RaS

 

[Guest]

Finally, I used the correct formula and got the results needed. Thank you so much for your "Patience" and time....RAS