Flag the latest correct value from grouped rows with criteria

[dazmisc]

Help please.

Looking for a formula to define col D.
I want to know the latest known 'Value' from colC (where it is not 0, UNLESS there is no other value). Grouped by 'ID' (ColA), and ordered by date (ColB)

To get the latest date I can use "=IF(MAXIFS(B:B,A:A,A2)=B2,"Current","")", but I need to exclude any rows that have a 0 value, unless there are no rows in a group that have anything other than a zero!?

 

[Fluff]

How about

=IF(AND(C2=MAXIFS(C:C,A:A,A2),COUNTIFS(A$2:A2,A2,C$2:C2,C2)=1),"Correct","")

 

[dazmisc]

Thanks Fluff, sorry I should have inlcuded another scenario in this to show why the Date col is important...

 

[Fluff]

Ok, how about

=IF(B2=MAXIFS(B:B,A:A,A2,C:C,IF(SUMIFS(C:C,A:A,A2),">0",">=0")),"Correct","")

 

[dazmisc]

Ok, how about

=IF(B2=MAXIFS(B:B,A:A,A2,C:C,IF(SUMIFS(C:C,A:A,A2),">0",">=0")),"Correct","")

Brill, thanks very much!

 

[Fluff]

You're welcome & thanks for the feedback.