Thread: Building calculations based on dates identified based on 2 other criteria

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.

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...

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.