counting

  • Thread starter Thread starter afdmello
  • Start date Start date
A

afdmello

is there a way to count two entries in a cell

A B C
1 4,5 3

counting the three cells should be= 4 is it possible

afd
 
No bob Column B is two numbers 4 and 5.
if I use the count formula it returns 3 as 4 and 5 are counted as 1
 
Hi
The array entered formula
{=SUM(LEN((SUBSTITUTE(A1:C1,",",""))))}
will give the correct result, PROVIDING that all of your numbers are single
digit.

To enter or amend an array formula, use Control+Shift+Enter (CSE) not just
Enter.
Do not type the curly braces { } yourself. When you use CSE, Excel will
insert them around your formula.

--
Regards
Roger Govier

afdmello said:
No bob Column B is two numbers 4 and 5.
if I use the count formula it returns 3 as 4 and 5 are counted as 1





__________ Information from ESET Smart Security, version of virus
signature database 4526 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4526 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hi. If all the numbers are separated by commas, you can try with the next
array formula:

=SUM(LEN(A1:C1)-LEN(SUBSTITUTE(A1:C1,",",""))+1)

Regards

Manolo
 
Roger

thanks for the input. However, the number inside the cells will range from 1
to 31 How can can I use your suggestion to them.

Thank you for the input

Afd
 
Manolo
I entered your formula.
In the cell it is showing #value
but when I click on the fx button it is showing the result as 4 can you
direct me towards the error please

afd
 
Hi afd.

It's an array formula. To enter it, use Control+Shift+Enter, not just Enter.

Regards

Manolo
 
Hello,

I suggest not to count empty cells as 1. Array-enter:
=COUNTA(A1:C1)+SUM(LEN(A1:C1)-LEN(SUBSTITUTE(A1:C1,",","")))

Of course, ",," would be counted as 3.

Regards,
Bernd
 
Back
Top