if search formula incl date range
 

I need to write a formula to do the following and it's just feeling way over
my head. I know I need an "if" at the beginning but then I get lost. Can
anyone help?

Col A Col B Col C Col D
Date $Amt Type Text Code

If the type in row 1 = X, find the minimum $Amt from all the rows where
Type=Y or Z, AND Date is within 30 days of the Date in row 1.

if search formula incl date range
 

Oops - forgot a criteria:

If the type in row 1 = X, find the minimum $Amt from all the rows where
Type=Y or Z, AND Date is within 30 days of the Date in row 1, ONLY SEARCHING
the rows where the Text Code is equal to the Text Code in row 1.

"Laury" wrote:

I need to write a formula to do the following and it's just feeling way over
my head. I know I need an "if" at the beginning but then I get lost. Can
anyone help?

Col A Col B Col C Col D
Date $Amt Type Text Code

If the type in row 1 = X, find the minimum $Amt from all the rows where
Type=Y or Z, AND Date is within 30 days of the Date in row 1.

if search formula incl date range
 

What I have working so far:
=IF(C2="MCD",MIN(IF($D$2:$D$13="1001",$B$2:$B$13)) ," ")

When I try to add an AND statement to make the min calculate on rows that
meet 2 criteria, it's not working:
=IF(C4="MCD",MIN(IF(AND($D$2:$D$13="1001",$C$2:$C$ 13="Private"),$B$2:$B$13))," ")

"Laury" wrote:

Oops - forgot a criteria:

If the type in row 1 = X, find the minimum $Amt from all the rows where
Type=Y or Z, AND Date is within 30 days of the Date in row 1, ONLY SEARCHING
the rows where the Text Code is equal to the Text Code in row 1.

"Laury" wrote:

I need to write a formula to do the following and it's just feeling way over
my head. I know I need an "if" at the beginning but then I get lost. Can
anyone help?

Col A Col B Col C Col D
Date $Amt Type Text Code

If the type in row 1 = X, find the minimum $Amt from all the rows where
Type=Y or Z, AND Date is within 30 days of the Date in row 1.

if search formula incl date range
 

Almost there but still need help on the date range part:
=IF(C2="MCD",MIN(IF(($D$2:$D$13="1001")*($C$2:$C$1 3<"MCD"),$B$2:$B$13))," ")

How do I add in a 3rd criteria that says pick the min amount that meets the
1st two criteria AND is within 30 days (before or after) the date on row 1?

"Laury" wrote:

What I have working so far:
=IF(C2="MCD",MIN(IF($D$2:$D$13="1001",$B$2:$B$13)) ," ")

When I try to add an AND statement to make the min calculate on rows that
meet 2 criteria, it's not working:
=IF(C4="MCD",MIN(IF(AND($D$2:$D$13="1001",$C$2:$C$ 13="Private"),$B$2:$B$13))," ")

"Laury" wrote:

Oops - forgot a criteria:

If the type in row 1 = X, find the minimum $Amt from all the rows where
Type=Y or Z, AND Date is within 30 days of the Date in row 1, ONLY SEARCHING
the rows where the Text Code is equal to the Text Code in row 1.

"Laury" wrote:

I need to write a formula to do the following and it's just feeling way over
my head. I know I need an "if" at the beginning but then I get lost. Can
anyone help?

Col A Col B Col C Col D
Date $Amt Type Text Code

If the type in row 1 = X, find the minimum $Amt from all the rows where
Type=Y or Z, AND Date is within 30 days of the Date in row 1.

if search formula incl date range
 

I got it working but thanks!

=IF(C2="MCD",MIN(IF(($D$2:$D$13=D2)*((A2-($A$2:$A$13))<31)*($C$2:$C$13<"MCD"),IF(($D$2:$D$ 13=D2)*((($A$2:$A$13)-A2)<31)*($C$2:$C$13<"MCD"),$B$2:$B$13,"
")," "))," ")

"Laury" wrote:

What I have working so far:
=IF(C2="MCD",MIN(IF($D$2:$D$13="1001",$B$2:$B$13)) ," ")

When I try to add an AND statement to make the min calculate on rows that
meet 2 criteria, it's not working:
=IF(C4="MCD",MIN(IF(AND($D$2:$D$13="1001",$C$2:$C$ 13="Private"),$B$2:$B$13))," ")

"Laury" wrote:

Oops - forgot a criteria:

If the type in row 1 = X, find the minimum $Amt from all the rows where
Type=Y or Z, AND Date is within 30 days of the Date in row 1, ONLY SEARCHING
the rows where the Text Code is equal to the Text Code in row 1.

"Laury" wrote:

I need to write a formula to do the following and it's just feeling way over
my head. I know I need an "if" at the beginning but then I get lost. Can
anyone help?

Col A Col B Col C Col D
Date $Amt Type Text Code

If the type in row 1 = X, find the minimum $Amt from all the rows where
Type=Y or Z, AND Date is within 30 days of the Date in row 1.