Building calculations based on dates identified based on 2 other criteria

[SeanMulholland]

Hello all! Hope that you can help.

I’m trying to build a formula that will identify the latest date in a list of unsorted data. The date is dependent on 2 other values.

Here is an example:

Date….......…….Department……….Flag
1/1/2010……….Department A……….Y
1/6/2010……….Department A……….N
1/3/2010……….Department B……….Y
1/10/2010……..Department A……….Y

I need to display the latest date for each department where the flag is Y.

Once this is figured out, I must also calculate the number of dates between that latest date for each department and another date on the sheet.

Any thoughts?

[DonkeyOte]

You can determine the MAX date using a MAX Array, ie:

=MAX(IF(($B$2:$B$5="Department A")*($C$2:$C$5="Y"),$A$2:$A$5))
confirmed with CTRL + SHIFT + ENTER

"Department A" can be replaced by a cell reference containing value of interest

(the above could be achieved with a Pivot Table of course)

As for your other question - we would need more info. but given use of XL2007 you should find COUNTIFS will do what you want.

[zbor]

But is the last data max data too?

Looking at the example I wouldn't say so because 1/3 is after 1/6...

Althought, it's for other department...

[SeanMulholland]

Actually, the pivot table turned out to be the right choice. Don't know why I didn't do that earlier.

Here is a new twist on the question though. I need to look back into the past to find the largest gap between dates in the data for events that have the Y flag, for each individual department. I need to present the longest departmental streaks that have occured in the data set, and compare it to the current streak that this pivot table helps me to define.

Defining the current current streak is pretty easy as the calculations are based on a single date field that I enter. Defining the longest previous streak is proving much more challenging, as I'm looking back at the entire dataset. Also remember that the data is not sorted in date order. I prefer to keep it that way because I hope to avoid adding additional steps in my download and analysis process.

My head hurts.