Countifs with 900 different criteria!!!

R

RUMPOLE

New Member
Joined
Oct 4, 2017
Messages
25
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.
 
Sort by date Sort by votes
shg said:
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.
Click to expand...

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.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
1=grass, 2=clay, ...

Any other mutually-exclusive columns might be likewise combined.
 
Last edited:
Upvote 0
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
BCDEFGH
2MyField1MyField2MyField3MyField4MyField5MyField6
3010100
4
5COUNT8
6
7IDMyField1MyField2MyField3MyField4MyField5MyField6
8a101101
9b010100
10c011000
11d101101
12e010100
13fOther01011
14g2Other0101
15h22Other010
Sheet1


After:


Book1
BCDEFGH
2MyField1MyField2MyField3MyField4MyField5MyField6
3010100
4
5COUNT2
6
7IDMyField1MyField2MyField3MyField4MyField5MyField6
9b010100
12e010100
Sheet1
 
Last edited:
Upvote 0
RUMPOLE said:
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.
Click to expand...
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?
 
Upvote 0
MEUserII said:
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?
Click to expand...

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
 
Upvote 0
uMhykDZ
uMhykDZ
MEUserII said:
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?
Click to expand...

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?
 
Last edited:
Upvote 0
StephenCrump said:
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:

BCDEFGH
COUNT
IDMyField1MyField2MyField3MyField4MyField5MyField6
a
b
c
d
e
f
g
h

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField6[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]Other[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]Other[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Other[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

</tbody>




After:

BCDEFGH
COUNT
IDMyField1MyField2MyField3MyField4MyField5MyField6
b
e

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]MyField6[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>
Click to expand...
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.
 
Upvote 0
RUMPOLE said:
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?!
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Countifs multiple criteria across a row
2 3
Replies
20
Views
744
Fluff
Fluff
Sangar_338
  • Solved
Methods for Quartile Calculation with Multiple Criteria
Replies
5
Views
799
Fluff
Fluff
I
  • Solved
CountIF alternative for performance improvements
2 3
Replies
21
Views
1K
ItalianPlatinum
I
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
158
BenW71
B
R
  • Question Question
Countifs - multiple criteria over workbook - multiple pages
Replies
4
Views
816
Georgiboy
Georgiboy

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top