0

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.

Improve this question
3
  • for starters, as far as I know there is no IFS formula in Excel. An explanation of what you are trying to do with you formula would be helpful. A formula on its own without context does not really tell anyone what you are doing with it. or wanting to do with it. When building up big formulas into a single cell it can be helpful to break down the formula into its separate parts in test cells to make sure each part is working and then pull them back in through their reference formulas (back substitute) until its all in one cell. Commented May 18, 2016 at 6:48
  • I take that back about the IFS statement. It is new to 2016 Commented May 18, 2016 at 6:59
  • 1
    MS Office description of IFS. What error are you getting with your formula? Commented May 18, 2016 at 7:03

1 Answer 1

Reset to default
0

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")))
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for your help! After realising that IFS is 2016 only, I am going to have to come up with another solution.
You can do it through a series of nested IF statements. my understanding of the IFS, is it only dealt with true conditions. With an IF statement you deal with false options on the false side of the IF formula.
That worked perfectly! Thanks so much for your help! I do have 2016 but the workbook would be used by others that may not, so thank you for the solution :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.