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

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

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

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

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

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