Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting mixed cells
I'd like to see if I can set up a counting system for counting the number of
occurances in a series of cells. For example, I have a row with the following (it's a team schedule): 1 x 3 5 x 10 3 x 9 11 x 12 4 x 5 I'd like to see how often a number would appear. In this case, the number 1 would appear 1 time, the number 3 appears twice. Thanks, Gil |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting mixed cells
Try this:
Data in A1:A5 C1 = number to count =SUMPRODUCT(--(ISNUMBER(FIND(" "&C1&" "," "&A1:A5&" ")))) Biff "Gil Vargas" <Gil wrote in message ... I'd like to see if I can set up a counting system for counting the number of occurances in a series of cells. For example, I have a row with the following (it's a team schedule): 1 x 3 5 x 10 3 x 9 11 x 12 4 x 5 I'd like to see how often a number would appear. In this case, the number 1 would appear 1 time, the number 3 appears twice. Thanks, Gil |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting mixed cells
That is some serious genius in your head!
That certainly did the trick. Thank you very much! Gil "Biff" wrote: Try this: Data in A1:A5 C1 = number to count =SUMPRODUCT(--(ISNUMBER(FIND(" "&C1&" "," "&A1:A5&" ")))) Biff "Gil Vargas" <Gil wrote in message ... I'd like to see if I can set up a counting system for counting the number of occurances in a series of cells. For example, I have a row with the following (it's a team schedule): 1 x 3 5 x 10 3 x 9 11 x 12 4 x 5 I'd like to see how often a number would appear. In this case, the number 1 would appear 1 time, the number 3 appears twice. Thanks, Gil |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting mixed cells
I found a bug!
If you have the same numbers in a cell like this: 1 x 1 10 x 10 They only get counted once. If that's not an issue then the formula I suggested will work. If this is an issue try this one: =SUMPRODUCT(--(LEFT(A1:A6,FIND(" x",A1:A6))+0=C1))+SUMPRODUCT(--(MID(A1:A6,FIND("x ",A1:A6)+2,255)+0=C1)) Biff "Gil Vargas" wrote in message ... That is some serious genius in your head! That certainly did the trick. Thank you very much! Gil "Biff" wrote: Try this: Data in A1:A5 C1 = number to count =SUMPRODUCT(--(ISNUMBER(FIND(" "&C1&" "," "&A1:A5&" ")))) Biff "Gil Vargas" <Gil wrote in message ... I'd like to see if I can set up a counting system for counting the number of occurances in a series of cells. For example, I have a row with the following (it's a team schedule): 1 x 3 5 x 10 3 x 9 11 x 12 4 x 5 I'd like to see how often a number would appear. In this case, the number 1 would appear 1 time, the number 3 appears twice. Thanks, Gil |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting mixed cells
Not an issue there. It is for round robin play in a billiard league and I
wanted to make sure all teams played at all locations without having to go cell by cell to check the figures. It is working great. Thank you very much. "Biff" wrote: I found a bug! If you have the same numbers in a cell like this: 1 x 1 10 x 10 They only get counted once. If that's not an issue then the formula I suggested will work. If this is an issue try this one: =SUMPRODUCT(--(LEFT(A1:A6,FIND(" x",A1:A6))+0=C1))+SUMPRODUCT(--(MID(A1:A6,FIND("x ",A1:A6)+2,255)+0=C1)) Biff "Gil Vargas" wrote in message ... That is some serious genius in your head! That certainly did the trick. Thank you very much! Gil "Biff" wrote: Try this: Data in A1:A5 C1 = number to count =SUMPRODUCT(--(ISNUMBER(FIND(" "&C1&" "," "&A1:A5&" ")))) Biff "Gil Vargas" <Gil wrote in message ... I'd like to see if I can set up a counting system for counting the number of occurances in a series of cells. For example, I have a row with the following (it's a team schedule): 1 x 3 5 x 10 3 x 9 11 x 12 4 x 5 I'd like to see how often a number would appear. In this case, the number 1 would appear 1 time, the number 3 appears twice. Thanks, Gil |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting mixed cells
You're welcome. Thanks for the feedback!
Biff "Gil Vargas" wrote in message ... Not an issue there. It is for round robin play in a billiard league and I wanted to make sure all teams played at all locations without having to go cell by cell to check the figures. It is working great. Thank you very much. "Biff" wrote: I found a bug! If you have the same numbers in a cell like this: 1 x 1 10 x 10 They only get counted once. If that's not an issue then the formula I suggested will work. If this is an issue try this one: =SUMPRODUCT(--(LEFT(A1:A6,FIND(" x",A1:A6))+0=C1))+SUMPRODUCT(--(MID(A1:A6,FIND("x ",A1:A6)+2,255)+0=C1)) Biff "Gil Vargas" wrote in message ... That is some serious genius in your head! That certainly did the trick. Thank you very much! Gil "Biff" wrote: Try this: Data in A1:A5 C1 = number to count =SUMPRODUCT(--(ISNUMBER(FIND(" "&C1&" "," "&A1:A5&" ")))) Biff "Gil Vargas" <Gil wrote in message ... I'd like to see if I can set up a counting system for counting the number of occurances in a series of cells. For example, I have a row with the following (it's a team schedule): 1 x 3 5 x 10 3 x 9 11 x 12 4 x 5 I'd like to see how often a number would appear. In this case, the number 1 would appear 1 time, the number 3 appears twice. Thanks, Gil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query counting cells in a row that exactly match cells in another | Excel Discussion (Misc queries) | |||
Subtotal counting formula in cells | Excel Discussion (Misc queries) | |||
Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions | |||
Counting filled cells in excel | Excel Worksheet Functions | |||
Counting Unique Cells When Spread Sheet is Filtered | Excel Worksheet Functions |