Re: Nested IF/AND with multiple columns

Dennis_Petrou · ‎Aug 17, 2021

Hey everyone! Thanks so much for all the help you’ve given, I’m still learning and this community has made it a great experience!

I think what I’m trying to do can be solved with nested IF statements, just can’t seem to find the definitive answer looking through old posts. Here’s what I want to do.

I want to calculate the number of hours it takes to do a certain job for a certain employee, where each employee takes a different amount of time for that job.

For example, Employee 1 takes 4 hours to do Task 1, but Employee 2 takes 3 hours to do the same task. There are a bunch of employees and a bunch different tasks, and the hours are different for each employee. Is there a not so messy way to do this?

Thanks so much for the support!

EDIT: this is the formula that I have that works for one Employee and one task

IF(
AND(
{Employee} = “Employee 1”,
TASK = “TASK 1”
),
“4”
)

Where it will show 4 hours for Employee 1 to do task 1.

Where I get confused is how to nest a whole lot of these to cover all possible coniditions. Basically, for each employee, there could be any one of 8 tasks assigned, and the hours for each employee differ for each task.

If there is a much easier way to do this, I’m all ears!

David_Solimini · ‎Aug 17, 2021

Sounds like you need a pivot table. Complete the grid as you have it in the image and then use the pivot table app to do the sums by task by employee.

Justin_Barrett · ‎Aug 17, 2021

There is. It’s called the SWITCH() function. It’s a much more compact method of generating output based on specific input. The basic format looks like this:

SWITCH(
    INPUT_SOURCE,
    OPTION_1, OUTPUT_1,
    OPTION_2, OUTPUT_2,
    OPTION_3, OUTPUT_3,
    ...,
    OPTIONAL_DEFAULT_OUTPUT
)

If INPUT_SOURCE matches OPTION_1, then the function returns OUTPUT_1; if it matches OPTION_2, then OUTPUT_2 is returned, and so on. If none of the comparison options are matched, it returns the OPTIONAL_DEFAULT_OUTPUT if one has been provided, or nothing if not.

In this case, I can see the formula being a series of nested SWITCH() functions:

SWITCH(
    Employee,
    "Employee 1", SWITCH(
        TASK,
        "TASK 1", 4,
        "TASK 2", 2,
        ...etc
    ),
    "Employee 2", SWITCH(
        TASK,
        "TASK 1", 3,
        "TASK 2", 5,
        ...etc
    ),
    ...etc
)

Dennis_Petrou · ‎Aug 17, 2021

Thank you for this, I will experiment!

Dennis_Petrou · ‎Aug 17, 2021

Never used that app, i will try it out. Thanks