IFS only pulling one of three criteria

[Cableguy918]

Having trouble getting IFS function to read the the options I have listed and for some reason it is only acknowledging this first. I'm not getting errors, just pulls the first option for all three values. I think my formula is correct, I just don't know what I'm missing. Anyone's help would be greatly appreciated. 

=IFS(D6>49,_25, D6>74,_40,D6>99,_50,TRUE,"0") I labeled the cells to shorten the formula.

Results should be

above 49 = 25

above 74 = 40

above 99 = 50

Above 49 works correctly but it stays 25 at 75, or 100, or any other number above 49. 

[#2 · Accepted solution]

@Cableguy918 

=IFS(D6>=99,50, D6>=74,40,D6>=49,25,TRUE,0)

The IFS formula returns the first result for which the condition is TRUE. For the value 99 condition D6>=99 is true therefore result is 50. For the value 74 condition D6>=74 is true therefore result is 40 and so on.

[#3]

Thank you so much, that is perfect!

[#4]

Would it then be possible to use AND in this formula?

=IFS(AND(C6=Data!B6),D6>=99,50, D6>=74,40,D6>=49,25,TRUE,0)

[#5]

@Cableguy918 

=IFS(AND(C6=Data!B6,D6>=99),50, D6>=74,40,D6>=49,25,TRUE,0)

Yes it's possible. Above formula returns the intended result in my sheet. I only changed the position of the closing bracket of the AND function. AND includes logical C6=Data!B6 and logical D6>=99 and the bracket is wrapped around both logicals.

[#6]

Thanks again, it looks like that fixed it, but doesn't remove the number when Data!B6 is not selected. Do I have to add a TRUE or FALSE to the AND section?

[#7]

@Cableguy918 

If conditions C6=Data!B6 and D6>=99 are true then the formula returns 50 even if C6 and Data!B6 are empty.

If you only want to return 50 if conditions C6=Data!B6 and D6>=99 and 'Data!B6 isn't empty' are true then you can apply this formula.

=IFS(AND(C6=Data!B6,D6>=99,NOT(ISBLANK(Data!B6))),50, D6>=74,40,D6>=49,25,TRUE,0)

or

=IFS(AND(C6=Data!B6,D6>=99,Data!B6<>""),50, D6>=74,40,D6>=49,25,TRUE,0)

NOT(ISBLANK(Data!B6)) and Data!B6<>"" are formulas which return the same result.