Counting mixed cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Try this:

Data in A1:A5

C1 = number to count

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

Biff
 
That is some serious genius in your head!

That certainly did the trick. Thank you very much!

Gil
 
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
 
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.
 
You're welcome. Thanks for the feedback!

Biff

Gil Vargas said:
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.
Click to expand...
 
Back
Top