| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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? |
|
#2
|
|||
|
|||
|
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? |
|
#3
|
|||
|
|||
|
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? |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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? |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| elseif formula | Excel Discussion (Misc queries) | |||
| Counting individual dates | Excel Discussion (Misc queries) | |||
| Counting Values | Excel Worksheet Functions | |||
| Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) | |||
| Counting... | Excel Worksheet Functions | |||
Linear Mode
