| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Criteria Range
I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range: =AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D4)) ) Column L is one of the columns I want to filter my data by but I also want to be able to filter other columns as well. Columns L thru P all have data that I need to filter and I though just simply changing the above to this would work: =AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$2,D 4))) But it didn't. How do I re-write this formula to be able to lookup up the data in multiple columns? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Range
If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula: =AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4))) Secret Squirrel wrote: I'm using an Advanced Filter to filter my data but I can't figure out how to set up my criteria range. I currently have this as my criteria range: =AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D4)) ) Column L is one of the columns I want to filter my data by but I also want to be able to filter other columns as well. Columns L thru P all have data that I need to filter and I though just simply changing the above to this would work: =AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$2,D 4))) But it didn't. How do I re-write this formula to be able to lookup up the data in multiple columns? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Range
Hi Debra,
Thank you, that worked perfectly. Now a followup question: I currently have two ways of filtering my data, first from cell D2, and then an optional one in E2. How would I re-write that formula to add a third, or even forth filter? Say I wanted to use F4 & G4 as additional filters. These two new filters would also use the same criteria range L4:P4. Thanks! "Debra Dalgleish" wrote: If you're looking for the value in E2 in any cell in L4:P4, you could use this formula: =AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4))) Secret Squirrel wrote: I'm using an Advanced Filter to filter my data but I can't figure out how to set up my criteria range. I currently have this as my criteria range: =AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D4)) ) Column L is one of the columns I want to filter my data by but I also want to be able to filter other columns as well. Columns L thru P all have data that I need to filter and I though just simply changing the above to this would work: =AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$2,D 4))) But it didn't. How do I re-write this formula to be able to lookup up the data in multiple columns? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Range
You could include a COUNTIF for each criteria cell:
=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2), COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4))) Secret Squirrel wrote: Hi Debra, Thank you, that worked perfectly. Now a followup question: I currently have two ways of filtering my data, first from cell D2, and then an optional one in E2. How would I re-write that formula to add a third, or even forth filter? Say I wanted to use F4 & G4 as additional filters. These two new filters would also use the same criteria range L4:P4. Thanks! "Debra Dalgleish" wrote: If you're looking for the value in E2 in any cell in L4:P4, you could use this formula: =AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4))) Secret Squirrel wrote: I'm using an Advanced Filter to filter my data but I can't figure out how to set up my criteria range. I currently have this as my criteria range: =AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D 4))) Column L is one of the columns I want to filter my data by but I also want to be able to filter other columns as well. Columns L thru P all have data that I need to filter and I though just simply changing the above to this would work: =AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$ 2,D4))) But it didn't. How do I re-write this formula to be able to lookup up the data in multiple columns? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Range
Thanks Debra! That worked perfectly!
"Debra Dalgleish" wrote: You could include a COUNTIF for each criteria cell: =AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2), COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4))) Secret Squirrel wrote: Hi Debra, Thank you, that worked perfectly. Now a followup question: I currently have two ways of filtering my data, first from cell D2, and then an optional one in E2. How would I re-write that formula to add a third, or even forth filter? Say I wanted to use F4 & G4 as additional filters. These two new filters would also use the same criteria range L4:P4. Thanks! "Debra Dalgleish" wrote: If you're looking for the value in E2 in any cell in L4:P4, you could use this formula: =AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4))) Secret Squirrel wrote: I'm using an Advanced Filter to filter my data but I can't figure out how to set up my criteria range. I currently have this as my criteria range: =AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D 4))) Column L is one of the columns I want to filter my data by but I also want to be able to filter other columns as well. Columns L thru P all have data that I need to filter and I though just simply changing the above to this would work: =AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$ 2,D4))) But it didn't. How do I re-write this formula to be able to lookup up the data in multiple columns? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Criteria Range
You're welcome! Thanks for letting me know that it worked.
Secret Squirrel wrote: Thanks Debra! That worked perfectly! "Debra Dalgleish" wrote: You could include a COUNTIF for each criteria cell: =AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2), COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4))) Secret Squirrel wrote: Hi Debra, Thank you, that worked perfectly. Now a followup question: I currently have two ways of filtering my data, first from cell D2, and then an optional one in E2. How would I re-write that formula to add a third, or even forth filter? Say I wanted to use F4 & G4 as additional filters. These two new filters would also use the same criteria range L4:P4. Thanks! "Debra Dalgleish" wrote: If you're looking for the value in E2 in any cell in L4:P4, you could use this formula: =AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4))) Secret Squirrel wrote: I'm using an Advanced Filter to filter my data but I can't figure out how to set up my criteria range. I currently have this as my criteria range: =AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2 ,D4))) Column L is one of the columns I want to filter my data by but I also want to be able to filter other columns as well. Columns L thru P all have data that I need to filter and I though just simply changing the above to this would work: =AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($ D$2,D4))) But it didn't. How do I re-write this formula to be able to lookup up the data in multiple columns? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sum a range after multiple criteria | Excel Discussion (Misc queries) | |||
| Any way to have a dynamic range for ranking, based on criteria? | Excel Worksheet Functions | |||
| How do I count data in range A:A that is dependent upon criteria . | Excel Worksheet Functions | |||
| use a date range as criteria in a countif formula | Excel Worksheet Functions | |||
| Specify a null value in an Excel Database criteria range | Excel Worksheet Functions | |||
Hybrid Mode
