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.

 

[RUMPOLE]

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.

Thanks for the suggestion shg.

I did consider his, but using a binary approach to the task, I believe I would need separate columns for each?

I was unsure how to go about solving the task with multiple possible selections in the one field, such as in the example you suggested above of having grass, clay, hard, and carpet combined in one field.

Kind Regards.

 

[shg]

1=grass, 2=clay, ...

Any other mutually-exclusive columns might be likewise combined.

 

[StephenCrump]

You could also look at Advanced Filter: http://www.contextures.com/xladvfilter01.html

MyFilters: =B2:H3
MyData: =B7:H15
C5: =SUBTOTAL(3,B8:B15)

Before:

Book1
	B	C	D	E	F	G	H
2		MyField1	MyField2	MyField3	MyField4	MyField5	MyField6
3		0	1	0	1	0	0
4
5	COUNT	8
6
7	ID	MyField1	MyField2	MyField3	MyField4	MyField5	MyField6
8	a	1	0	1	1	0	1
9	b	0	1	0	1	0	0
10	c	0	1	1	0	0	0
11	d	1	0	1	1	0	1
12	e	0	1	0	1	0	0
13	f	Other	0	1	0	1	1
14	g	2	Other	0	1	0	1
15	h	2	2	Other	0	1	0
Sheet1

After:

Book1
	B	C	D	E	F	G	H
2		MyField1	MyField2	MyField3	MyField4	MyField5	MyField6
3		0	1	0	1	0	0
4
5	COUNT	2
6
7	ID	MyField1	MyField2	MyField3	MyField4	MyField5	MyField6
9	b	0	1	0	1	0	0
12	e	0	1	0	1	0	0
Sheet1

 

[MEUserII]

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.

I am curious about this problem since I have a sketch of a solution in mind; would you please post a sample data set (post as an image of the Excel spreadsheet), so I can test my solution to make sure it's right?

 

[shg]

You could also look at Advanced Filter

That's a very good suggestion. Wish it had occurred to me.

 

[RUMPOLE]

I am curious about this problem since I have a sketch of a solution in mind; would you please post a sample data set (post as an image of the Excel spreadsheet), so I can test my solution to make sure it's right?

Hi

I'm having trouble creating a URL which it asks for to post the sample data, I'll post it once I've worked it out- it doesnt allow the option of browsing through the directory and then selecting unfortunately, as that is what i know how to do!

Kind Regards

 

[RUMPOLE]

I am curious about this problem since I have a sketch of a solution in mind; would you please post a sample data set (post as an image of the Excel spreadsheet), so I can test my solution to make sure it's right?

I've tried uploading an excel image via URL- unsure if it will work

edit- it hasn't worked- is it possible I don't have permission to post an image?

 

[RUMPOLE]

You could also look at Advanced Filter: http://www.contextures.com/xladvfilter01.html

MyFilters: =B2:H3
MyData: =B7:H15
C5: =SUBTOTAL(3,B8:B15)

Before:

	B	C	D	E	F	G	H
2		MyField1	MyField2	MyField3	MyField4	MyField5	MyField6
3		0	1	0	1	0	0
4
5	COUNT	8
6
7	ID	MyField1	MyField2	MyField3	MyField4	MyField5	MyField6
8	a	1	0	1	1	0	1
9	b	0	1	0	1	0	0
10	c	0	1	1	0	0	0
11	d	1	0	1	1	0	1
12	e	0	1	0	1	0	0
13	f	Other	0	1	0	1	1
14	g	2	Other	0	1	0	1
15	h	2	2	Other	0	1	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

After:

	B	C	D	E	F	G	H
2		MyField1	MyField2	MyField3	MyField4	MyField5	MyField6
3		0	1	0	1	0	0
4
5	COUNT	2
6
7	ID	MyField1	MyField2	MyField3	MyField4	MyField5	MyField6
9	b	0	1	0	1	0	0
12	e	0	1	0	1	0	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Thank you very much, I will check out the advanced filters link.

Did you automate the step to get to the filtered 'after' screenshot in the example above? if so, could you suggest a way i could replicate the automation?!

Thanks and Kind Regards again.

 

[StephenCrump]

Did you automate the step to get to the filtered 'after' screenshot in the example above? if so, could you suggest a way i could replicate the automation?!

I didn't in this case, but it's easily done from VBA.

The macro recorder will get you started. As usual, the code generated will be a little bit flabby, but is easily modified once you've got the basic syntax.