Excel Min/Max Date with multiple conditions 2

[Pappalito]

I feel this one is almost solved but have been working on it the past 2 hrs so I am putting it to the community.
I have a worksheet called "Master" with the following relevant columns
Column B: Location (3 letter state abbreviation e.g. AKA)
Column D: Type (VAR, VARAD, MMR-AD)
Column I: Order Date
Column J: Funding Type (options: VFC, 317)

On a new worksheet I am attempting to determine the earliest and latest Order Date (J) for each Location/Type/Funding Type combination.

Working to ID these dates for the AKA/VAR/VFC combination this is the formula I have:
=IF(AND(Master!B:B ="AKA",Master!D ="VAR",Master!J:J ="VFC"),MINA(Master!I:I),"0")

The problem is that this keeps returning "0" even though there are rows that contain the combination required with dates.

Any help would be greatly appreciated.

 

[xlbo]

Your "easiest" way to do this is to put a pivot table over your data.

3 Row fields:
Location
Type
Funding Type

2 Value Fields:
Min of Date
Max of Date

you can then drag the value fields over to the column headers to show min / max on a single row

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[byundt]

If you have Excel 2007 or later, you can use an array-entered formula like:
=MIN(IF((Master!B:B ="AKA")*(Master!D ="VAR")*(Master!J:J ="VFC"),Master!I:I,""))
To array-enter a formula:
1) Hold Control and Shift keys down
2) Press Enter
3) Release all three keys
Excel should respond by adding curly braces { } surrounding the formula. If not (or if the formula returns #VALUE!) then click in the formula bar and repeat steps 1-3 again.

If you have Excel 2003 or earlier, you must restrict the range of cells to less than a full column. For example:
=MIN(IF((Master!B2:B1000 ="AKA")*(Master!D21000 ="VAR")*(Master!J2:J1000 ="VFC"),Master!I2:I1000,""))
The reference to row 1000 is arbitrary and may extend beyond your data.

By way of explanation, MIN ignores text like the empty string ="" if the IF is not satisfied. Multiplying Boolean expressions is equivalent to an And operation on a row by row basis. So take the minimum of the values in column I only if columns B, D and J satisfy the other three criteria on that same row.

Brad

 

[Pappalito]

Thanks so much XLBO and Byundt. I ended up going w/ the solution Byundt suggested b/c it fit into the format best. It works great. Best to you both.
P