IF, AND, checkboxes, and ranges

[Greg_Davies]

I’m trying to write formula for a field that would check the {CHECKBOX} field for a check (=1), and also check another {FINAL AMOUNT} field for a range, to then return a corresponding value. If the {CHECKBOX} is empty (=0), I’d like the field to just return a “0”.

I’ve tried tons of different ways to nest IF and AND statements, and the only formula I tried that will not trigger a syntax error will not display the values if both the checkbox and range criteria are met:

IF(AND({CHECKBOX}=1,{FINAL AMOUNT}<20000,500,IF(AND({CHECKBOX}=1,{FINAL AMOUNT}>=20000,{FINAL AMOUNT}<40000,750,IF(AND({CHECKBOX}=1,{FINAL AMOUNT}>=40000,1000))))))

If I try to add one more IF statement (either at the beginning or the end) to yield the “0” if {CHECKBOX}=0, Airtable tells me it’s invalid:

IF({CHECKBOX}=0,0,IF(AND({CHECKBOX}=1,{FINAL AMOUNT}<20000,500,IF(AND({CHECKBOX}=1,{FINAL AMOUNT}>=20000,{FINAL AMOUNT}<40000,750,IF(AND({CHECKBOX}=1,{FINAL AMOUNT}>=40000,1000)))))))

I’ve read tons of different IF, AND support threads and none of them (that I’ve found) deal with ranges, so I would LOVE some help. Thanks!

[augmented · Accepted solution]

Hi Greg. I think the formula below will work for you.

IF({CHECKBOX}, IF({FINAL AMOUNT}<20000,500, IF({FINAL AMOUNT}<40000,750, 1000)),0)

Good luck!

[Greg_Davies]

Wow. I was clearly working overtime to get this to work. I was building off a previous formula that WAS working with the IF, AND combo, but when I tried to get the other field involved, it wouldn’t work. Clearly even though my first formula did work, I was working WAY too hard to get it to do its thing. THANK YOU!!!