google-sheetsgoogle-sheets-formula

MINIFS with Multiple Conditions

I need to extract the lowest value from a range with multiple conditions. I used the below MINIFS formula but I am getting the wrong results.

=MINIFS(B10,E10,H10,K10, C10,F10,I10,L10,"Valid")

I need to check two (2) conditions as follows:

=MINIFS(B10,E10,H10,K10, C10,F10,I10,L10,"Valid", C10,F10,I10,L10,"Expiring soon")

You can find the data and formula in the below Google Sheet:

SPSP CERT-01 EXP. DATE SPSP CERT-01 REM. DAYS SPSP CERT-01 STATUS SPSP CERT-02 EXP. DATE SPSP CERT-02 REM. DAYS SPSP CERT-02 STATUS SPSP CERT-03 EXP. DATE SPSP CERT-03 REM. DAYS SPSP CERT-03 STATUS SPSP CERT-04 EXP. DATE SPSP CERT-04 REM. DAYS SPSP CERT-04 STATUS SPSP CERT-05 EXP. DATE SPSP CERT-05 REM. DAYS
31/Oct/2025 226 Valid -45735 No Active License -45735 No Active License -45735 No Active License -45735
25/Aug/2025 159 Valid 25/Aug/2025 159 Valid 25/Aug/2025 159 Valid 26/Nov/2026 617 Valid -45735
12/Apr/2027 754 Valid 05/Apr/2025 17 Expiring soon 05/Apr/2025 17 Expiring soon -45735 No Active License -45735
30/Dec/2026 651 Valid 28/Mar/2025 9 Expiring soon -45735 No Active License -45735 No Active License -45735
27/Mar/2025 8 Expiring soon 28/Mar/2025 9 Expiring soon 12/Apr/2027 754 Valid -45735 No Active License -45735

https://docs.google.com/spreadsheets/d/1_jOn0vyv6osaoppeU9TXX1boOlZbnoCFy3eWsddP7uc/edit?gid=1604939792#gid=1604939792

Please help me to resolve this issue.

Thank you so much in advance..

Solution

  • Here's one approach which you may adapt accordingly:

    =minifs(A10:M10,index(xmatch(B10:N10,{"Valid","Expiring Soon"})^0),1)

    enter image description here