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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...
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
Click to expand...
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
Click to expand...
cell and give me the total of all the cells for the same
row, not the sum.
 
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
 
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
 
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
 
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
 
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
 
Finally, I used the correct formula and got the results needed. Thank you so much for your "Patience" and time....RAS
 
Back
Top