Multiple Criteria in Custom AutoFilter

G

greg0226

New Member
Joined
Feb 24, 2002
Messages
31
Hi everyone,

is there a way after you have put an autofilter on a list of data to have more than 2 criteria in the custom autofilter?

I have 4 different districts that i need to have filtered in a huge list and when i go to custom under autofilter i can only select equal to two different districts but i need to be able to do 4. Is this possible and if not does anyone have any good ideas?

Thanks,
Greg
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Hi Greg,

yup... advanced filter

insert 5 or 6 rows above your data, then copy the existing column headings from your data into the first new row

underneath the appropriate header, just type your criteria in

so, say if you had :

Region Amount
North 111
South 222
East 333
West 444
Central 555

you'd put Region and amount in your new empty rows, then just North, South, East and West in each cell underneath "Region"

(this is your criteria range)

now goto data, filter, advanced filter

filter the list inplace

list range = your data range(including headings)

criteria range = the above new criteria range

click okay

this should filter the database, as per your list of 4 regions sitting above it

and hey : that's an advanced function; so you're technically creeping into the advanced range..... time to smile smugly at your boss

:biggrin:
 
Upvote 0
so it's something like this prior to the advanced filter being invoked :

<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%><TR><TD COLSPAN=8 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT><FONT COLOR=WHITE>Microsoft Excel - Book1</FONT></TD><TD ALIGN=RIGHT><FONT COLOR=WHITE SIZE=2>___Running: xl97 : OS = Windows (32-bit) 4.90</FONT></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=8><TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%><TR><TD>(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp</TD><TD ALIGN=RIGHT VALIGN=MIDDLE><FORM NAME='formCb7552370787'><INPUT TYPE='Button' NAME='btCb5705426622' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb0487398093.sltNb8859926925.value);'></FORM></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=8><TABLE BORDER=0><TR><Form name='formFb0487398093'><TD WIDTH=5% ALIGN=CENTER BGCOLOR=White><SELECT NAME='sltNb8859926925' onChange='document.formFb0487398093.txbFb1960511720.value = document.formFb0487398093.sltNb8859926925.value'><option value=3>A1</select></TD><TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 >=</TD><TD ALIGN=LEFT BGCOLOR=White><input type='text' name='txbFb1960511720' size='120' value=District></TD></form></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>D</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>E</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>F</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>G</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>1</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>District</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Amount</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>North</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>South</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>4</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>East</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>5</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>West</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>6</CENTER></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>7</CENTER></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>8</CENTER></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>9</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>District</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Amount</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>10</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>North</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>11</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>South</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>12</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>East</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>13</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>West</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>14</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Central</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>5</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>15</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>North</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>6</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>16</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>South</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>5</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>17</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>East</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>18</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>West</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>55</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>19</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Central</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER><CENTER>20</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>North</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD COLSPAN=8><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT>
<FONT COLOR=RED SIZE=2>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs.</FONT>

<FONT COLOR=#339966 SIZE=1>The above image was automatically generated by [HtmlMaker 2.0�@BETA]</FONT><FONT COLOR=#339966 SIZE=1>If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm>click here</A> to download</FONT>
<FONT COLOR=#339966 SIZE=1>This code was graciously allowed to be modified: by All credit to <A HREF=mailto:corosuke@chan.co.jp>Colo</FONT>
</CENTER>
 
Upvote 0
Thanks that worked perfect. I really appreciate that. I think i might have to make my boss aware of this. Thanks for the idea.

Greg
 
Upvote 0
On 2002-07-22 10:33, greg0226 wrote:
Hi everyone,

is there a way after you have put an autofilter on a list of data to have more than 2 criteria in the custom autofilter?

I have 4 different districts that i need to have filtered in a huge list and when i go to custom under autofilter i can only select equal to two different districts but i need to be able to do 4. Is this possible and if not does anyone have any good ideas?

Thanks,
Greg
Click to expand...
Hi Greg,
Chris is right - the solution is AdvancedFilter.
You can also use the formula shown in D2 as Criteria. Read carefully the help for this topic. When using formula as Criteria for advanced filter, you must include 2 cells in the Criteria range (D1:D2) and D1 must not contain any Heading.

Eli

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=6><FONT COLOR=WHITE>Microsoft Excel - Book1_______________Running: xl97 : OS = Windows (32-bit) 4.10</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>D2</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=3 BGCOLOR=White>=OR(A3="DIST1",A3="DIST2",A3="DIST3",A3="DIST4")</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FF9900> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DATA</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FF9900 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=OR(A3="DIST1",A3="DIST2",A3="DIST3",A3="DIST4")")><FONT FACE=Arial COLOR=#000000>TRUE</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST1</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>3</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DATA</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST1</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>1</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST5</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>5</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST2</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST6</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>6</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST3</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>3</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>9</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST7</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>7</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST4</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>4</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>10</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>8</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST4</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>8</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>11</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST1</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>9</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST1</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>9</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>12</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>10</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST2</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>10</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>13</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>11</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST3</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>11</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>14</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>12</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST4</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>12</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>15</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST5</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>13</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST2</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>19</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>16</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST6</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>14</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST3</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>20</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>17</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST7</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>15</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST4</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>21</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>18</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST5</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>16</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST2</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>22</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>19</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST6</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>17</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST3</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>23</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>20</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST7</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>18</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST4</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>24</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>21</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>19</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>22</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>20</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>23</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>21</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>24</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>22</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>25</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>23</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>26</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>DIST4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>24</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=6><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>To see the formula in the cells just click on the cells hyperlink</FONT>

<FONT COLOR=#339966 SIZE=1>The above image was automatically generated by [HtmlMaker V1.23]</FONT>
<FONT COLOR=#339966 SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you.</FONT>
<FONT COLOR=#339966 SIZE=1>This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo</FONT>
</CENTER>
 
Upvote 0
Eli,

if you like the functionality of this, I'd recommend you have a look at the Database Formulae excel has (DSUM, DGET etc etc....they all begin with D)

I use them all the time now and find them very handy, maybe less fiddly than lots of IFs and ORs

they follow the same logic : adding a criteria range, based on your existing column headings - the resulting formulae tend to read more sensibly too :

=DSUM(mydata,amount,A1:C3)
 
Upvote 0
Is there any way to do this if I wanted to filter based on a "contains" function? Thanks,

Brian
 
Upvote 0
Brian,

Did you ever get a response here to your question.

I would like the opposite...does not contain.

Deb
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA Autofilter IF the criteria does not existing
Replies
6
Views
269
Alexsay
A
excelbytes
  • Solved
COUNTIFS With Lists Of Multiple Criteria
Replies
5
Views
212
Fluff
Fluff
B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
497
Alex Blakenburg
A
A
  • Question
Lookup multiple customers and apply their respective slabs
2 3
Replies
21
Views
517
Dave Patton
D
G
  • Solved
Custom Filter based on list
Replies
1
Views
92
RoryA
RoryA

Forum statistics

Threads
1,215,002
Messages
6,122,652
Members
449,092
Latest member
peppernaut

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