How to calculate a sum conditionally based on the values of two other columns-CodePudding

Feels like this may be a basic, but I cannot find a way to do this with sumifs.

I've got four columns in one table, representing the workload of an employee like this:

Job	Employee # 1	Employee # 2	Workload
Job 1	Bob	Jane	5
Job 2	Bob		2
Job 3	Jane	Susan	3
Job 4	Susan		2

I'd like to output total workflow results to a second sheet for each employee based on a specialized formula. In English, the forumla would be:

Calculate the total workload for each employee. - For each job that includes that includes employee named "X" and no assigned teammate, use the job's corresponding workload value. - For each job that includes that includes employee named "X" and has an assigned teammate, reduce the corresponding workload value by 50%.

So with the given table above, I'd want an output like this:

Employee Name	Workload
Bob	4.5
Jane	4
Susan	3.5

Math:
Bob = ((job_1 / 2)   job_2)
Jane = ((job_1 / 2)   (job_3 / 2))
Susan = ((job_3 / 2)   job_4)

Does anyone know how I can accomplish this?
Functions like sumifs seem to only let me set criteria to sum or not sum a value. But I cannot find a clear way to sum only 50% of a value based on a condition in a separate column.

CodePudding user response：

=LAMBDA(name,SUMIFS(D2:D5,B2:B5,name,C2:C5,"") SUMIFS(D2:D5,B2:B5,name,C2:C5,"<>")/2 SUMIFS(D2:D5,C2:C5,name)/2)("Bob")

The math is

SUM D, if B is Bob and C is empty and
SUM half of D if B is Bob and C is not empty and
SUM half of D, if C is Bob.

Or the same logic via query:

=QUERY(
    {
      QUERY(B1:D5,"Select B,sum(D) where C is null group by B");
      QUERY(B1:D5,"Select C,sum(D)/2 where C is not null group by C");
      QUERY(B1:D5,"Select B,sum(D)/2 where C is not null group by B")
    },
    "Select Col1, sum(Col2) where not Col1 contains 'Employee' group by Col1"
)

However, note that we're assuming title contains Employee and no other names contain Employee.

Employee # 1	sum sum Workload
Bob	4.5
Jane	4(Incorrect in the question)
Susan	3.5

CodePudding user response：

Use this formula

=ArrayFormula({ "Employee Name", "Workload";
              QUERY(
 QUERY({LAMBDA(a,b,c,d,k, {b,a,{d/k};c,a,{d/k}} )
             (A2:A,B2:B,C2:C,D2:D,
              BYROW(B2:C, LAMBDA(c, IF(COUNTA(c)=0,,IF(COUNTA(c)=1,1,2)))))},
        "Select (Col1),sum(Col3) Group by Col1" ,0), "Where Col1 <> '' ",0)})

Used formulas help
ARRAYFORMULA - QUERY - LAMBDA - BYROW - IF - COUNTA - SUM