| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count some of the numbers in a cell
Hello,
can someone please help. I have a spreadsheet that have a "random" set of numbers in cells A:A. Many of the numbers in A:A have numbers that starts with a fixed number e.g 12345 the next 5 numbers is random. Some of the others starts with 23456 the next 5 is random. 12345 and 23456 is the only leftmost numbers in A:A Like this 1234503256 1234501204 1234599652 2345600369 2345698005 In C1 I want it to show 3 (count of numbers that starts with 12345) In D1 I want it to show 2 (count of numbers that starts with 23456) I have tried to combine countif and left function without solving it, obviously. 8-) Using Office 2007 Regards Jon |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count some of the numbers in a cell
Jon
Try =COUNTIF(A1:A5,"1234500000")-COUNTIF(A1:A5,"2345600000") in C1 and =COUNTIF(A1:A5,"2345600000") in D1. Good luck. Ken Norfolk, Va On Jul 3, 5:35 pm, Full Name wrote: Hello, can someone please help. I have a spreadsheet that have a "random" set of numbers in cells A:A. Many of the numbers in A:A have numbers that starts with a fixed number e.g 12345 the next 5 numbers is random. Some of the others starts with 23456 the next 5 is random. 12345 and 23456 is the only leftmost numbers in A:A Like this 1234503256 1234501204 1234599652 2345600369 2345698005 In C1 I want it to show 3 (count of numbers that starts with 12345) In D1 I want it to show 2 (count of numbers that starts with 23456) I have tried to combine countif and left function without solving it, obviously. 8-) Using Office 2007 Regards Jon |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count some of the numbers in a cell
=SUMPRODUCT(--(LEFT(A1:A5,5)="12345"))
=SUMPRODUCT(--(LEFT(A1:A5,5)="23456")) -- p45cal "Full Name" wrote: Hello, can someone please help. I have a spreadsheet that have a "random" set of numbers in cells A:A. Many of the numbers in A:A have numbers that starts with a fixed number e.g 12345 the next 5 numbers is random. Some of the others starts with 23456 the next 5 is random. 12345 and 23456 is the only leftmost numbers in A:A Like this 1234503256 1234501204 1234599652 2345600369 2345698005 In C1 I want it to show 3 (count of numbers that starts with 12345) In D1 I want it to show 2 (count of numbers that starts with 23456) I have tried to combine countif and left function without solving it, obviously. 8-) Using Office 2007 Regards Jon |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count based on cell value between two numbers | Excel Discussion (Misc queries) | |||
| how to count if a cell value falls between two numbers | Excel Worksheet Functions | |||
| Count numbers in the same cell | Excel Discussion (Misc queries) | |||
| Count numbers of Month in a cell | Excel Worksheet Functions | |||
| Function to count numbers in one Cell IE 1+2+10 | Excel Worksheet Functions | |||
Linear Mode
