counting

[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

 

[Bob I]

Column B is text.

 

[afdmello]

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

 

[Roger Govier]

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

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

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

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Manolo]

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

 

[afdmello]

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

 

[afdmello]

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

 

[Manolo]

Hi afd.

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

Regards

Manolo

 

[Bernd P]

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