IF Function Formula Multiple Criteria

[Joko Purnomo]

Inst No. (A)	Day Overdue (B)
12​	24​
12​	7​
12​	24​
12​	24​
9​	106​
11​	32​
12​	3​
10​	65​
11​	23​
11​	31​
11​	23​
11​	15​
9​	76​
11​	14​
11​	7​
10​	40​
6​	159​
11​	4​
10​	27​
11​	4​
10​	34​
9​	61​
10​	26​
5​	164​
9​	41​
9​	38​
9​	30​
9​	38​
4​	186​
9​	30​
9​	28​
9​	14​
9​	23​
3​	199​
9​	14​
5​	133​
9​	7​
9​	6​
7​	65​
9​	4​
9​	4​
9​	3​
7​	54​
5​	121​
8​	24​
8​	14​
7​	44​
7​	24​
8​	8​
7​	9​
6​	42​
7​	11​
4​	102​
7​	9​
7​	7​
7​	5​
7​	4​
7​	3​
5​	56​
6​	24​
6​	10​
5​	35​
5​	30​
4​	56​
5​	7​
4​	24​
2​	25​

Column A = Inst No.
Column B = Day Overdue

Column C = If column A <=6, and column B > 7 and <= 30, then "7+ (1-6)"
Column C = If column A <=6, and column B > 30, then "30+ (1-6)"
Column C = If column A > 6 and <= 12, and column B > 30, then "30+ (7-12)"

Help me friend to find the formula above. Thank for helping

 

[SanjayGMusafir]

Column C = If column A <=6, and column B > 7 and <= 30, then "7+ (1-6)"
Column C = If column A <=6, and column B > 30, then "30+ (1-6)"
Column C = If column A > 6 and <= 12, and column B > 30, then "30+ (7-12)"

Check this and revert -

=If(And(A2<=6,B2>7,B2<=30),"7+(1-6)",if(And(A2<=6,B2>30),"30+(1-6)",if(And(A2>6,A2<=12,B2>30),"30+(7-12)","")))

 

[Joe4]

You can put this formula in cell C2 and copy down for all rows. Note that you have a bunch of rows that do not meet any of these conditions:

=IF(AND(A2<=6,B2>7,B2<=30),"7+ (1-6)",IF(AND(A2<=6,B2>30),"30+ (1-6)",IF(AND(A2>6,A2<=12,B2>30),"30+ (7-12)","")))

EDIT: Looks like Sanjay and I came up with the same formula just seconds apart!

 

[Peter_SSs]

Column C = If column A <=6, and column B > 7 and <= 30, then "7+ (1-6)"
Column C = If column A <=6, and column B > 30, then "30+ (1-6)"
Column C = If column A > 6 and <= 12, and column B > 30, then "30+ (7-12)"

You have not covered all the possibilities.
For example what if..

• column A <=6, and column B <=7
• column A > 6 and <= 12, and column B <=30