I'm working on data tracker to show the most recent activity date with two conditions

Date ___ State ___ City

8/8 ___ KY ___ Lexington

8/9 ___ KY ___ Lexington

8/9 ___ KY ___ Louisville

8/10 ___ GA ___ Atlanta

8/11 ___ KY ___ Frankfort

On another sheet is the overview, to show the most recent activity date without looking through all the incoming data

So essentially the overview would be pulling if State (Column B) is TRUE, then if City (Column C) is TRUE, then the most recent date is found in Column A and then show on the overview on the next sheet.

CodePudding user response：

If I understand you correctly, you want to be able to specify a city and state on your Summary sheet, and pull in the newest date associated to those two values.

Activity Sheet:

Summary Sheet:

=MAXIFS('Activity Sheet'!A2:A6,'Activity Sheet'!B2:B6,Summary!B2,'Activity Sheet'!C2:C6,Summary!A2)

CodePudding user response：

Do you mean you want the earliest (lowest) date for a given city/state combination? So your results would be on the summary page?

I'd suggest combining XLOOKUP with an embedded MIN for the date, returning the city, state, and date. To create a unique list of the city and states on the summary page, try:

SORT(UNIQUE(FILTER(detail_range, detail_range<>""))) 

to have the list spill and avoid any null values.