MAXIFS Help

Support
1

Hi, I am in need of help using the MAXIFS Function.
I’m referencing a subtable with a Date Field (A70), and a Select Field (B70)
I need the last date if the select field is either Perfection Ltr-Accepted or Perfection Ltr-Deficient
I’ve tried several variations including using either one as standalone:
MAXIFS(A70,B70,“Perfection Ltr-Deficient”) Working
MAXIFS(A70,B70,“Perfection Ltr-Accepted”) Working

However if I try to combine to get the latest date of either selection, it doesn’t work.

IF(B70=‘Perfection Ltr-Accepted’,MAXIFS(A70,B70,‘Perfection Ltr-Accepted’),IF(B70=‘Perfection Ltr-Deficient’,MAXIFS(A70,‘Perfection Ltr-Deficient’),‘’) Does not work

MAXIFS(A70,B70,IF(B70=‘Perfection Ltr-Accepted’,B70,IF(B70=‘Perfection Ltr-Deficient’,B70,‘’))) Works but pull the wrong date

MAXIFS(A70,B70,IF(B70=‘Perfection Ltr-Accepted’,‘Perfection Ltr-Accepted’,IF(B70=‘Perfection Ltr-Deficient’,‘Perfection Ltr-Deficient’,‘’))) Works but pull the wrong date

IF(B70=‘Perfection Ltr-Accepted’, MAXIFS(A70,B70,‘Perfection Ltr-Accepted’),IF(B70=‘Perfection Ltr-Deficient’, MAXIFS(A70,B70,‘Perfection Ltr-Deficient’),‘’)) Doesn’t Work

Is there a way for this to work? Or am I just being brain numb after hours of trying to get this to work and am missing something easy.
Thank you for you help
Robyn

2

Hi Robyn,

Are both A70 and B70 fields in the subtable? And are MAXIFS(A70, B70, “Perfection Ltr-Deficient”) and MAXIFS(A70, B70, “Perfection Ltr-Accepted”) also applied to subtable fields?

If they are, when you use the IF() formula with the subtable fields as conditions, an error occurs. The IF() formula, when used in regular fields, can only determine if the regular field meets the condition and cannot individually evaluate conditions for subtable fields. Therefore, please use the MAX() formula separately for MAXIFS(A70, B70, “Perfection Ltr-Deficient”) and MAXIFS(A70, B70, “Perfection Ltr-Accepted”) to obtain the maximum date, and then use the IF() formula to set the conditions and obtain the desired value.

If they are not, please provide screenshots for us to better determine the cause of the formula error.

Sincerely,
Benson.