Picking the latest date from a multiple criteria

[Corleone]

hello all - hopefully someone can help
im currently using the formula below to look up and populate dates based on 2 criteria being matched

=SUMPRODUCT(('RAW DATA'!$C$3:$C$224=CATEGORIES!$D$8)*('RAW DATA'!$D$3:$D$224=B65)*'RAW DATA'!$F$3:$F$224)
, for example
the formula is looking for the finish date in column f in the "RAW DATA" Tab based on the following conditions

If column c in the "Raw Data" table matches the entry in cell D8 of the "CATEGORIES" Tab
and
If column D in the "Raw Data" table matches the entry in cell B65 of the current tab im in

This works fine if there is only 1 instance where this criteria is matched, however when there is more than one it adds the combined dates together - so you get something like 4/5/2028

I think i need to change the bit at the end of the formula *'RAW DATA'!$F$3:$F$224) so that it looks for the latest date
where this criteria is met

Any help appreciated as always

 

[BGY23]

Hi,

What I think you need is a Max(if formula so you pull back the latest date from a list based on two criteria. I will use my own data but you can adjust the formula to fit you range;


Criteria Cells H1 and I1
Date Range to return H6:J11

Formula = Max(if($H$6:$H$11=$c$1, if($I$6:$I$11=$I$1,$j$6:$j$11)

When you've typed the formula for your ranges you must Pres Ctrl Shift and Enter together. Not just enter which results in Value#



Good luck

 

[Corleone]

SPOT ON!!

Really appreciate your Help BGY23