Counting individual #s
 

I need to count one number in a number sequence for cells a1:a20. Im trying
to count the number "2" in every cell, but every cell has 4 numbers. How do I
count only one of those numbers in every cell?

Counting individual #s
 

Supose we start with:
1254
9521
7895
3574
1258
2210
First run text to columns to pick apart the individual digits, we see:
1254 1 2 5 4
9521 9 5 2 1
7895 7 8 9 5
3574 3 5 7 4
1258 1 2 5 8
2210 2 2 1 0
Next in cell F1 enter: =(B1=2)+(C1=2)+(D1=2)+(E1=2) and copy down
We now see:
1254 1 2 5 4 1
9521 9 5 2 1 1
7895 7 8 9 5 0
3574 3 5 7 4 0
1258 1 2 5 8 1
2210 2 2 1 0 2
Somewhere enter =SUM(F:F) and the result will be 5


--
Gary's Student


"Mike" wrote:

I need to count one number in a number sequence for cells a1:a20. Im trying
to count the number "2" in every cell, but every cell has 4 numbers. How do I
count only one of those numbers in every cell?

Counting individual #s
 

How do you get the numbers in individual columns?

"Gary''s Student" wrote:

Supose we start with:
1254
9521
7895
3574
1258
2210
First run text to columns to pick apart the individual digits, we see:
1254 1 2 5 4
9521 9 5 2 1
7895 7 8 9 5
3574 3 5 7 4
1258 1 2 5 8
2210 2 2 1 0
Next in cell F1 enter: =(B1=2)+(C1=2)+(D1=2)+(E1=2) and copy down
We now see:
1254 1 2 5 4 1
9521 9 5 2 1 1
7895 7 8 9 5 0
3574 3 5 7 4 0
1258 1 2 5 8 1
2210 2 2 1 0 2
Somewhere enter =SUM(F:F) and the result will be 5


--
Gary's Student


"Mike" wrote:

I need to count one number in a number sequence for cells a1:a20. Im trying
to count the number "2" in every cell, but every cell has 4 numbers. How do I
count only one of those numbers in every cell?

Counting individual #s
 

So if you had this in A1:A20:

7887
9078
0650
2222
3620
2389
6653
9583
7377
3391
7961
4067
2913
9477
5401
5365
1697
4048
9004
8613

You'd want to get an answer of 7?

If yes:
=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,"2","")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.




Mike wrote:

I need to count one number in a number sequence for cells a1:a20. Im trying
to count the number "2" in every cell, but every cell has 4 numbers. How do I
count only one of those numbers in every cell?

--

Dave Peterson

Counting individual #s
 

With the help of a wizard.

Select column A and pull-down:
Data Text to Columns...
Select delimited and separate each digit. For the destination, select
column B. The wizard will fill B, C, D, and E for you.
--
Gary's Student


"Mike" wrote:

How do you get the numbers in individual columns?

"Gary''s Student" wrote:

Supose we start with:
1254
9521
7895
3574
1258
2210
First run text to columns to pick apart the individual digits, we see:
1254 1 2 5 4
9521 9 5 2 1
7895 7 8 9 5
3574 3 5 7 4
1258 1 2 5 8
2210 2 2 1 0
Next in cell F1 enter: =(B1=2)+(C1=2)+(D1=2)+(E1=2) and copy down
We now see:
1254 1 2 5 4 1
9521 9 5 2 1 1
7895 7 8 9 5 0
3574 3 5 7 4 0
1258 1 2 5 8 1
2210 2 2 1 0 2
Somewhere enter =SUM(F:F) and the result will be 5


--
Gary's Student


"Mike" wrote:

I need to count one number in a number sequence for cells a1:a20. Im trying
to count the number "2" in every cell, but every cell has 4 numbers. How do I
count only one of those numbers in every cell?

Counting individual #s
 

I think using the text to columns is the thing I should do, but I have no
delimiters seperating my numbers. How do I use the wizard in this case?

"Dave Peterson" wrote:

So if you had this in A1:A20:

7887
9078
0650
2222
3620
2389
6653
9583
7377
3391
7961
4067
2913
9477
5401
5365
1697
4048
9004
8613

You'd want to get an answer of 7?

If yes:
=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,"2","")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.




Mike wrote:

I need to count one number in a number sequence for cells a1:a20. Im trying
to count the number "2" in every cell, but every cell has 4 numbers. How do I
count only one of those numbers in every cell?

--

Dave Peterson

Counting individual #s
 

One more thing, I have to many columns to individually input delimiters. How
do I go about seperating these numbers?

"Dave Peterson" wrote:

So if you had this in A1:A20:

7887
9078
0650
2222
3620
2389
6653
9583
7377
3391
7961
4067
2913
9477
5401
5365
1697
4048
9004
8613

You'd want to get an answer of 7?

If yes:
=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,"2","")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.




Mike wrote:

I need to count one number in a number sequence for cells a1:a20. Im trying
to count the number "2" in every cell, but every cell has 4 numbers. How do I
count only one of those numbers in every cell?

--

Dave Peterson

Counting individual #s
 

Don't use delimited.

Use Fixed Width.

Then draw a line between each character.

But try that formula first.

Mike wrote:

One more thing, I have to many columns to individually input delimiters. How
do I go about seperating these numbers?

"Dave Peterson" wrote:

So if you had this in A1:A20:

7887
9078
0650
2222
3620
2389
6653
9583
7377
3391
7961
4067
2913
9477
5401
5365
1697
4048
9004
8613

You'd want to get an answer of 7?

If yes:
=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,"2","")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.




Mike wrote:

I need to count one number in a number sequence for cells a1:a20. Im trying
to count the number "2" in every cell, but every cell has 4 numbers. How do I
count only one of those numbers in every cell?

--

Dave Peterson


--

Dave Peterson

Counting individual #s
 

That formula worked.Thankyou

"Dave Peterson" wrote:

Don't use delimited.

Use Fixed Width.

Then draw a line between each character.

But try that formula first.

Mike wrote:

One more thing, I have to many columns to individually input delimiters. How
do I go about seperating these numbers?

"Dave Peterson" wrote:

So if you had this in A1:A20:

7887
9078
0650
2222
3620
2389
6653
9583
7377
3391
7961
4067
2913
9477
5401
5365
1697
4048
9004
8613

You'd want to get an answer of 7?

If yes:
=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,"2","")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.




Mike wrote:

I need to count one number in a number sequence for cells a1:a20. Im trying
to count the number "2" in every cell, but every cell has 4 numbers. How do I
count only one of those numbers in every cell?

--

Dave Peterson


--

Dave Peterson