# If/And Formula Help - Multiple column values

Topic Labels: Formulas
Solved
1613 2
cancel
Showing results for
Did you mean:  4 - Data Explorer

Hi AirTable Community -

I have a grid with one single select column indicating contributors (Freelance Writers) and another column indicating Word Count (#). The Word Count field will be an integer, but the cost calculation will depend on where the word count falls within a numerical range. I’m hoping to generate a formula that generates the cost of the article based on the word count. It would read like this:

If {Freelance Writer} is “option from single select column” and {Word Count} >=500 and {Word Count} is <=700, Cost “150”

but I am striking out. Any advice on what I’m missing?

1 Solution

Accepted Solutions  14 - Jupiter

I assume you will have multiple options within `{Freelance Writer}` and also multiple options within `{Word Count}`, so to make that a little less messy, we can use a `SWITCH()` function in combination with `IF()` statements, or even nested `SWITCH()` functions. I think something like this should work:

``````SWITCH(
{Freelance Writer},
"Option 1",
IF(
{Word Count} >= 1500,
300,
IF(
{Word Count} >= 1000,
250,
IF(
{Word Count} >= 750,
200,
IF(
{Word Count} >= 500,
150,
100
)
)
)
),
"Option 2",
IF(
{Word Count} >= 1200,
260,
IF(
{Word Count} >= 900,
240,
IF(
{Word Count} >= 700,
200,
150
)
)
)
)
``````

The `SWITCH()` function evaluates the first parameter you pass it - in this case we told it to look at the `{Freelance Writer}` field - and matches it against the output options you pass it in subsequent parameter spots. In this case, we passed `"Option 1"` and `"Option 2"` as the possible values of the `{Freelance Writer}` field we want to act against.

If the `SWITCH()` function determines that the field `{Freelance Writer}` matches the output `"Option 1"`, it will execute the nested `IF()` statement we made right after `"Option 1"` – and that nested `IF()` is running down possible options for the output of the `{Word Count}` field. When one of those conditions matches, you get your final output for the field. I put the `{Word Count}` options in descending order (the largest word count threshold - 1500 - first) so that they can be evaluated simply without having to define both an upper and lower limit for each threshold; if `{Word Count}` isn’t `>= 1500`, then it moves down into the next threshold category, namely `>=1000`, and if it doesn’t meet that threshold then it moves down into the next, until it finds a hit and returns your dollar amount.

Hopefully that makes sense enough that you can plug in your own data and expand on it.

2 Replies 2  14 - Jupiter

I assume you will have multiple options within `{Freelance Writer}` and also multiple options within `{Word Count}`, so to make that a little less messy, we can use a `SWITCH()` function in combination with `IF()` statements, or even nested `SWITCH()` functions. I think something like this should work:

``````SWITCH(
{Freelance Writer},
"Option 1",
IF(
{Word Count} >= 1500,
300,
IF(
{Word Count} >= 1000,
250,
IF(
{Word Count} >= 750,
200,
IF(
{Word Count} >= 500,
150,
100
)
)
)
),
"Option 2",
IF(
{Word Count} >= 1200,
260,
IF(
{Word Count} >= 900,
240,
IF(
{Word Count} >= 700,
200,
150
)
)
)
)
``````

The `SWITCH()` function evaluates the first parameter you pass it - in this case we told it to look at the `{Freelance Writer}` field - and matches it against the output options you pass it in subsequent parameter spots. In this case, we passed `"Option 1"` and `"Option 2"` as the possible values of the `{Freelance Writer}` field we want to act against.

If the `SWITCH()` function determines that the field `{Freelance Writer}` matches the output `"Option 1"`, it will execute the nested `IF()` statement we made right after `"Option 1"` – and that nested `IF()` is running down possible options for the output of the `{Word Count}` field. When one of those conditions matches, you get your final output for the field. I put the `{Word Count}` options in descending order (the largest word count threshold - 1500 - first) so that they can be evaluated simply without having to define both an upper and lower limit for each threshold; if `{Word Count}` isn’t `>= 1500`, then it moves down into the next threshold category, namely `>=1000`, and if it doesn’t meet that threshold then it moves down into the next, until it finds a hit and returns your dollar amount.

Hopefully that makes sense enough that you can plug in your own data and expand on it.  4 - Data Explorer

Jeremy, you’re a wizard! Absolutely works:

SWITCH({Freelance Writer}, “Carrie”, IF({Word Count} >= 2501, 650, IF({Word Count} >= 2101, 575, IF({Word Count} >= 1801, 500, IF({Word Count} >= 1501, 425, IF({Word Count} >= 1301, 350, IF({Word Count} >= 1001, 275, IF({Word Count} >= 701, 200, IF({Word Count} >= 500, 150)))))))), “Vanessa”, IF({Word Count} >= 1501, 350, IF({Word Count} >= 1301, 275, IF({Word Count} >= 1001, 200, IF({Word Count} >= 701, 150, IF({Word Count} >= 500, 125))))))

Thank you so so much! 