Combining Multiple If(And( Statements

Mily Oti

Hello,

I'm trying to create a formula that will look at a language, the agency, and the status and fill in $ amount. I have written six different formulas that all work but when I try to combine them I get the error message, incorrect argument set. Here are the formulas:

Sign Language - BY

=IF(STATUS@row = "", "", IF(AND([1ST]@row = "BY", LANGUAGE@row = "Sign Language", STATUS@row = "Confirmed w/ expectations"), 2 * 55))

=IF(STATUS@row = "", "", IF(AND([1ST]@row = "BY", LANGUAGE@row = "Sign Language", STATUS@row = "Confirmed late"), 2 * 55))

Spanish - P

=IF(STATUS@row = "", "", IF(AND([1ST]@row = "P", LANGUAGE@row = "Spanish", STATUS@row = "Confirmed w/ expectations"), 2 * 34))

=IF(STATUS@row = "", "", IF(AND([1ST]@row = "P", LANGUAGE@row = "Spanish", STATUS@row = "Confirmed late"), 2 * 34))

 Not Spanish - P

=IF(STATUS@row = "", "", IF(AND([1ST]@row = "P", LANGUAGE@row <> "Spanish", STATUS@row = "Confirmed w/ expectations"), 2 * 55))

=IF(STATUS@row = "", "", IF(AND([1ST]@row = "P", LANGUAGE@row <> "Spanish", STATUS@row = "Confirmed late"), 2 * 55))

Any help will be really appreciated,

Mily Oti

Jeff Hoelzel

=IF([1ST]@row="","",IF(LANGUAGE@row = "", "", IF(AND(LEFT(STATUS@row, 3) = "Con", OR([1ST]@row = "P", [1ST]@row = "By"), LANGUAGE@row <> "Spanish"), 2 * 55, 2 * 34))

Jeff Hoelzel

=IF(AND(LANGUAGE@row = "Spanish", [1ST]@row = "P", STATUS@row = "Confirmed w/ expectations"), 2 * 55, "")

Mily Oti

There's more variables than that. I have to use all of the different variables shown in my formulas in order to charge the correct amount. Thanks for the answer, though.

Jeff Hoelzel

=IF(AND(LEFT([STATUS]@row, 3) = "Con", [1ST]@row = "P", [LANGUAGE]@row > 0), 2 * 55, "")

Mily Oti

Thank you, for the LEFT function. That's great. This works fine except that not all languages charge the same. The rate for Spanish is 34 and for all others is 55. There are also two different variables for [1ST], BY and P and 3 language choices Spanish, Sign Language, and <> Spanish. So now I need to combine the following 3 formulas:

=IF(STATUS@row = "", "", (IF(AND(LEFT([STATUS]@row, 3) = "Con", [1ST]@row = "BY", LANGUAGE@row = "Sign Language"), 2 * 55))

=IF(STATUS@row = "", "", (IF(AND(LEFT([STATUS]@row, 3) = "Con", [1ST]@row = "P", LANGUAGE@row = "Spanish"), 2 * 34))

=IF(STATUS@row = "", "", (IF(AND(LEFT([STATUS]@row, 3) = "Con", [1ST]@row = "P", LANGUAGE@row <> "Spanish"), 2 * 55))

Thanks for taking the time to look at this.

Jeff Hoelzel

=IF([1ST]@row="","",IF(LANGUAGE@row = "", "", IF(AND(LEFT(STATUS@row, 3) = "Con", OR([1ST]@row = "P", [1ST]@row = "By"), LANGUAGE@row <> "Spanish"), 2 * 55, 2 * 34))

Mily Oti

Thank you! This is excellent. Thank you for all of your time and expertise. Have a great weekend!

Mily Oti