Countifs with 900 different criteria!!!

[RUMPOLE]

Can anyone suggest a way I can do this?

I get up to about 120 criteria before excel tells me that the formula is too big.

Any suggestions of methods I could use to expand it to 900 (I know this sounds excessive)?

Kind Regards.

 

[StephenCrump]

Perhaps you could explain in a bit more detail what you're trying to do?

Potentially this will open it up to a smarter solution.

 

[shg]

There is surely a better way. How does this come about?

 

[Peter h]

Not sure how you could possibly have 900 different criteria you are counting, but you could try breaking it down into different cells and then just sum those cells. Maybe?

 

[RUMPOLE]

Perhaps you could explain in a bit more detail what you're trying to do?

Potentially this will open it up to a smarter solution.

I have a data set which has 900 different columns in it, for EACH tennis match in the last year ie. say 4000 matches in the each with 900 columns of data. Each match has 900 columns (the same 900 for every match).

Most of them are 'Binary', ie. if the particular criteria in each column was fulfilled, then this would be signified with a '1'. If it wasn't fulfilled, then there would be a '0'.

Say for example I have another tennis match tomorrow, which has a '1' in 35 of the 900 columns.

I want to find out how many tennis matches in the last year have the same 35 filters with a '1' in them (by looking through my data set which has 900 different columns in it, for tennis matches for the last year), as the match happening tomorrow does.

I was hoping to do this by countifs, but I'm sure there is a smarter way!

Kind Regards.

 

[StephenCrump]

Do you just want a count, or would it be useful to see the filtered results?

Also, are you open to a VBA solution, or do you want only Excel formulae?

 

[RUMPOLE]

It would be useful to see the filtered results but if a count was the only thing available, that'd be fine as well.

Definitely open to a VBA solution, I just didnt have advanced enough VBA skills to do it myself, although I'm 'ok?!' on VBA!

Kind Regards.

PS THANKS TO ALL FOR THE INTEREST AND TIME TAKEN TO CONSIDER MY PROBLEM!!

 

[shg]

You could do a 900 way sort in VBA -- quick to code, might take a bit to run.

If 1 and 0 are equally likely in each column, it is beyond unlikely that any two rows would match in 35 positions.

 

[RUMPOLE]

"If 1 and 0 are equally likely in each column, it is beyond unlikely that any two rows would match in 35 positions.2

They are not equally likely in each column, each column will have a different probability to the other. ie. one column may be "surface type- grass", another column may be- "first serves returned over 50%"

 

[shg]

That was just an observation, irrelevant to the solution.

But perhaps some of those columns could be fruitfully combined. For example, instead of having separate columns for grass, clay, hard, and carpet, you could have one column for surface type.