MIFS with complex COUNTIF formula inside

[Leticia]

Hi! I am trying to find the oldest date in KPI!$G:$G meeting all the conditions of either these two formulas (I obtained the same result)

- COUNTIFS(KPI!$P:$P;"PENDIENTE";KPI!$G:$G;"<="&$F$4)+COUNTIFS(KPI!$G:$G;"<="&$F$4;KPI!$Q:$Q;">"&$F$4)-IFERROR(IFS($G$4="YES";SUM(COUNTIFS(KPI!$R:$R;{"R.";"7250"};KPI!$G:$G;"<="&$F$4;KPI!$Q:$Q;">"&$F$4;KPI!$Q:$Q;"<"&$F$4+30)));0)
- IF($G$4="YES";COUNTIFS(KPI!$P:$P;"PENDIENTE";KPI!$G:$G;"<="&$F$4)+COUNTIFS(KPI!$G:$G;"<="&$F$4;KPI!$Q:$Q;">"&$F$4)-SUM(COUNTIFS(KPI!$R:$R;{"R.";"7250"};KPI!$G:$G;"<="&$F$4;KPI!$Q:$Q;">"&$F$4;KPI!$Q:$Q;"<"&$F$4+30));COUNTIFS(KPI!$P:$P;"PENDIENTE";KPI!$G:$G;"<="&$F$4)+COUNTIFS(KPI!$G:$G;"<="&$F$4;KPI!$Q:$Q;">"&$F$4))

Thank you!

 

[Radoslaw Poprawski]

minisheet pls.

 

[Leticia]

minisheet pls.

Hi! I dont know why it does not let me extract the minisheet.
but here it would be simplified. THANK YOU!

=IF($G$4="YES";COUNTIFS('EV80'!$C:$C;"PENDIENTE";'EV80'!$B:$B;"<="&$F$4)+COUNTIFS('EV80'!$B:$B;"<="&$F$4;'EV80'!$D:$D;">"&$F$4)-SUM(COUNTIFS('EV80'!$E:$E;{"R.";"7250"};'EV80'!$B:$B;"<="&$F$4;'EV80'!$D:$D;">"&$F$4;'EV80'!$D:$D;"<"&$F$4+30));COUNTIFS('EV80'!$C:$C;"PENDIENTE";'EV80'!$B:$B;"<="&$F$4)+COUNTIFS('EV80'!$B:$B;"<="&$F$4;'EV80'!$D:$D;">"&$F$4))

 

[Leticia]

minisheet pls.

=IF($G$4="YES";MIN(MINIFS('EV80'!$G:$G;'EV80'!$P:$P;"PENDIENTE";'EV80'!$G:$G;"<="&$F$4);MINIFS('EV80'!$G:$G;'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4);IF($G$4="YES";MINIFS('EV80'!$G:$G;'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4;'EV80'!$Q:$Q;"<"&$F$4+30;'EV80'!$R:$R;"R.");"");IF($G$4="YES";MINIFS('EV80'!$G:$G;'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4;'EV80'!$Q:$Q;"<"&$F$4+30;'EV80'!$R:$R;"7250");""));MIN(MINIFS('EV80'!$G:$G;'EV80'!$P:$P;"PENDIENTE";'EV80'!$G:$G;"<="&$F$4);MINIFS('EV80'!$G:$G;'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4)))

Having obtained this formula, I want to exclude the minimum values obtained from
IF($G$4="YES";MINIFS('EV80'!$G:$G;'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4;'EV80'!$Q:$Q;"<"&$F$4+30;'EV80'!$R:$R;"R.");"");IF($G$4="YES";MINIFS('EV80'!$G:$G;'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4;'EV80'!$Q:$Q;"<"&$F$4+30;'EV80'!$R:$R;"7250");"")), instead of including them too