Using multiple criteria for one IFS criteria

I would like to use multiple criteria for one criteria in an IFS formula.

=IFS(COUNTIFS('DATA'!$A:$A,$A17,'DATA'!$M:$M,"02 - B")=0,"0",(COUNTIFS('DATA'!$A:$A,$A17,'DATA'!$C:$C,"A - Open",'DATA'!$M:$M,"02 - B"))>0,"1",(COUNTIFS('DATA'!$A:$A,$A17,'DATA'!$C:$C,"A - Open",'DATA'!$M:$M,"02 - B")=0)  **(This is where I get stucK) &** ((COUNTIFS('DATA'!$A:$A,$A17,'DATA'!$C:$C,"D - Closed",'DATA'!$M:$M,"02 - B"))<(COUNTIFS('DATA'!$A:$A,$A17,'DATA'!$M:$M,"02 - B"))),"2")

I could potentially want to use more than 2 different criteria for the one 1 IFS criteria to be met.

Solution

You had a few extra brackets in your formula. I cant test this as I do not have your data or know what you were trying to achieve with your formula, BUT if you were trying to combine two conditions that had to be true, then you would use the AND statement and separate each condition with a , as I did in your edited formula:

=IFS(COUNTIFS('DATA'!$A:$A,$A17,'DATA'!$M:$M,"02 - B")=0,"0",
  COUNTIFS('DATA'!$A:$A,$A17,'DATA'!$C:$C,"A - Open",'DATA'!$M:$M,"02 - B")>0,"1",
  AND(COUNTIFS('DATA'!$A:$A,$A17,'DATA'!$C:$C,"A - Open",'DATA'!$M:$M,"02 - B")=0,
  COUNTIFS('DATA'!$A:$A,$A17,'DATA'!$C:$C,"D - Closed",'DATA'!$M:$M,"02 - B")<COUNTIFS('DATA'!$A:$A,$A17,'DATA'!$M:$M,"02 - B")),"2")

Since you apparently do not have 2016, you could try the following nested IF statements:

=IF(COUNTIFS(DATA!$A:$A,$A17,DATA!$M:$M,"02 - B")=0,"0", IF(COUNTIFS(DATA!$A:$A,$A17,DATA!$C:$C,"A - Open",DATA!$M:$M,"02 - B")>0,"1", IF(AND(COUNTIFS(DATA!$A:$A,$A17,DATA!$C:$C,"A - Open",DATA!$M:$M,"02 - B")=0,COUNTIFS(DATA!$A:$A,$A17,DATA!$C:$C,"D - Closed",DATA!$M:$M,"02 - B")<COUNTIFS(DATA!$A:$A,$A17,DATA!$M:$M,"02 - B")),"2","No Conditions Met")))