Help with IF AND EXCEL Formula

[pattih]

My formula IF(AND((K4,"Non-Discretionary,M2<44926,"Priority 1",IF(AND((K4,"Discretionary,L4,Discretionary","Priority 3","Not Prioritized")

is returning #VALUE

What I want the formula to do: if k4 = non-discretionary and M2 is less than 12/31/2022, put Priority 1, if K4=discretionary and L4 = discretionary, put Priority 3, and if none of those are true, put "Not Prioritized

for the date column (M), it is formatted as a date, so I wasn't sure i i put in the actual date or the value in the formula.

 

[DSCfromCFA]

G'day Pattih,
You have a number of issues to do with the parentheses, quotation marks and seperators (commas)

For example, K4,"Non-Discretionary,M2
should be K4="Non-Discretionary",M2

You also open 6 parentheses, and only close 1 - you actually only need to open 2 and close 2 for your formula to work correctly

I have tested the following and it works
=IF(AND(K4="Non-Discretionary",M2<44926),"Priority 1",IF(AND(K4="Discretionary",L4="Discretionary"),"Priority 3","Not Prioritized"))

 

[DSCfromCFA]

Clearly I am not good at either counting or seeing parentheses - open and close four sets of parentheses.

 

[pattih]

G'day Pattih,
You have a number of issues to do with the parentheses, quotation marks and seperators (commas)

For example, K4,"Non-Discretionary,M2
should be K4="Non-Discretionary",M2

You also open 6 parentheses, and only close 1 - you actually only need to open 2 and close 2 for your formula to work correctly

I have tested the following and it works
=IF(AND(K4="Non-Discretionary",M2<44926),"Priority 1",IF(AND(K4="Discretionary",L4="Discretionary"),"Priority 3","Not Prioritized"))

Thank you so much for you help! I have copied the formula to my spreadsheet and it worked like a charm! Perfect!