Forum Discussion
Solved
Stopping a MAX function after one result.
I'm using a MAX function with 2 ranges =MAX(IF(ActDName=$A3,ActDDate)). Which I'm using to bring back the most recent date where a development plan is in place. In essence, it looks up a person...
Your Excel version doesn't support MAXIFS().
Use AGGREGATE() instead.
=IF(COUNTIFS(ActDName,A3,ActionDevelopment!$F$2:$F$14,"Yes"),"",AGGREGATE(14,6,ActionDevelopment!$D$2:$D$14/(ActDName=A3),1))
Sample file attached. As you can see, on the ActionDevelopment tab, I've resolved the most recent plan dates for user 1 and 2 (rows 13 and 14). On the 2018 Team Overview Sheet, I want it to show this as blank but instead it's bringing back the next latest date, which is not resolved e.g. row 11 and 12
I think you didn't explained it correctly.
Try this:
=IF(COUNTIFS(ActDName,A3,ActionDevelopment!$F$2:$F$14,"Yes"),"",MAXIFS(ActionDevelopment!$D$2:$D$14,ActDName,A3))
Thank you. I've tried that and I get a #VALUE error response
The ranges are not the same size.
- Sorry, I don't understand. Both range of cells in column D and column F are the same. On my sheet they would be D2:D300 and F2:F300 .
Is that what you mean?