Counting mixed cells

[Guest]

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

 

[Biff]

Try this:

Data in A1:A5

C1 = number to count

=SUMPRODUCT(--(ISNUMBER(FIND(" "&C1&" "," "&A1:A5&" "))))

Biff

 

[Guest]

That is some serious genius in your head!

That certainly did the trick. Thank you very much!

Gil

 

[Biff]

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

 

[Guest]

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]

You're welcome. Thanks for the feedback!

Biff

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.