If/And Formula Help - Multiple column values

#1

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?

#2

Hi @Lieren_Stuivenvolt_A,

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 Likes
#3

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!

1 Like