Need formula for multiple conditions in 3 columns

have a spreadsheet with several columns -- some data, some dates.
Does anyone know how I can create a formula to do the equivalent of
filtering on three
different columns with a different date for each column filtered? For
example I have four columns of information but only want to extract a
certain criteria where as Date A is greater than a specific date, Date
B = another date and Date C = yet another date.
Date A Date B Date C Data
2/12/2001 2/23/2006 3/31/2006 Apples
2/23/2006 1/1/1900 Pears
3/16/2004 2/23/2006 3/31/2006 Peaches
6/15/2005 2/23/2006 6/30/2006 Grapes

For example, I'd like to be able to extract all records where Date A >
1/1/1900 and Date B = 2/23/2006, and Date C = 3/31/2006.


I have Excel 2003
Any help would be greatly appreciated.

Add a helper column with a formula of

=AND(A2>DATE(1900,1,1),B2=DATE(2006,2,23),C2=DATE(2006,3,31))

and filter on TRUE

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Mildred" <[email protected]> wrote in message
news:[email protected]...
> have a spreadsheet with several columns -- some data, some dates.
> Does anyone know how I can create a formula to do the equivalent of
> filtering on three
> different columns with a different date for each column filtered? For
> example I have four columns of information but only want to extract a
> certain criteria where as Date A is greater than a specific date, Date
> B = another date and Date C = yet another date.
> Date A Date B Date C Data
> 2/12/2001 2/23/2006 3/31/2006 Apples
> 2/23/2006 1/1/1900 Pears
> 3/16/2004 2/23/2006 3/31/2006 Peaches
> 6/15/2005 2/23/2006 6/30/2006 Grapes
>
> For example, I'd like to be able to extract all records where Date A >
> 1/1/1900 and Date B = 2/23/2006, and Date C = 3/31/2006.
>
>
> I have Excel 2003
> Any help would be greatly appreciated.
>

With datalist in A1 to D100, and the dates of interest entered in E1 to E3
respectively, try this *array* formula:

=INDEX($D$1:$D$100,SMALL(IF(($A$1:$A$100>$E$1)*($B$1:$B$100=$E$2)*($C$1:$C$100=$E$3),ROW($1:$100)),ROWS($1:1)))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

NOW ... after the initial CSE entry, copy this formula down as many rows as
you anticipate that there will be data returned.

When this formula runs out of data to return, you will get a #NUM! error.

You should therefore make sure that you have *at least one error*, in order
to insure that *all* possible data is being returned.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Mildred" <[email protected]> wrote in message
news:[email protected]...
> have a spreadsheet with several columns -- some data, some dates.
> Does anyone know how I can create a formula to do the equivalent of
> filtering on three
> different columns with a different date for each column filtered? For
> example I have four columns of information but only want to extract a
> certain criteria where as Date A is greater than a specific date, Date
> B = another date and Date C = yet another date.
> Date A Date B Date C Data
> 2/12/2001 2/23/2006 3/31/2006 Apples
> 2/23/2006 1/1/1900 Pears
> 3/16/2004 2/23/2006 3/31/2006 Peaches
> 6/15/2005 2/23/2006 6/30/2006 Grapes
>
> For example, I'd like to be able to extract all records where Date A >
> 1/1/1900 and Date B = 2/23/2006, and Date C = 3/31/2006.
>
>
> I have Excel 2003
> Any help would be greatly appreciated.
>