Counting individual #s

M

Mike

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?
 
G

Gary''s Student

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
 
D

Dave Peterson

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.
 
G

Gary''s Student

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.
 
M

Mike

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?
 
M

Mike

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

Dave Peterson

Don't use delimited.

Use Fixed Width.

Then draw a line between each character.

But try that formula first.
 
Top