Excel array formula for max_date

问题:

I'm trying to use an array formula:

=MAX(IF(C2<>0,IF($B$2:$B$29600=B2,$A$2:$A$29600),NA))

on below data set (just an example):

The issue here that I'm missing is not only to get MAX date groupped by specific ID (when revenue is not missing), but to have this date shown only for revenue higher than 0.
So in the case of id = 'x' max date should be 1/9/2019 as this is last date that had any revenue present, and not 1/11/2019 - this is max date but has 0 revenue.

I'd appreciate any help!

回答1:

Try this:

=IF(C2<>0;MAX(IF($B$2:$B$17=B2;IF($C$2:$C$17=MAX(IF($B$2:$B$17=B2;$C$2:$C$17));$A$2:$A$17)));NA())

This formula is an array formula, so it must be inserted with ENTER+SHIFT+CTRL

I get this:

IMPORTANT: Please note my Excel is in spanish, so my default date format is dd/mm/yyyy and that explains why formulas and dates are not in english.