Search code examples
google-sheetstabular

Query two spreadsheet tables and sum the results

I have a schedule table for each agent and another table for the type of users that each agent handles, i want to query and sum the results so that i can find out how many agents will handle each type of users everyday. Also i want to differentiate between 7AM,9AM and the evening shift. Worth mentioning that both tables are of different sizes so i couldn't use Filter function. Sample of the two tables i'm querying and the result table i'm looking for How can i do this in spreadsheets?

I tried to use the filter function but didn't succeed because both tables are of different sizes also i don't know how to incorporate sumifs function with filter function plus i tried to use the importrange function since both tables are in different spreadsheets but failed

Solution

  • Added one possible solution to the test sheet for you check out:

    =map(J2:J,lambda(Σ,if(Σ="",,bycol(K1:U1,lambda(Γ,sum(map(filter('[Consolidated] Rotational Shifts'!A6:A46,filter('[Consolidated] Rotational Shifts'!B6:I46,'[Consolidated] Rotational Shifts'!B5:I5=Σ)=I1),lambda(Λ,countif(filter(E:H,D:D=Λ),Γ)))))))))

    enter image description here

    1. map(J2:J,lambda(Σ,if(Σ="",, : processing dates in J2:J one by one & Σ is identifier used for the date which can be used in later steps (you can change it as per your choice)

    2. bycol(K1:U1,lambda(Γ, : processing group headers in K1:U1 one by one

    3. filter('[Consolidated] Rotational Shifts'!A6:A46,filter('[Consolidated] Rotational Shifts'!B6:I46,'[Consolidated] Rotational Shifts'!B5:I5=Σ)=I1): filtering agent numbers in consolidated tab based on date from step 1

    4. countif(filter(E:H,D:D=Λ),Γ): filtering group names from columns_E_to_H based on agent numbers obtained in Step 3 and checks for the respective group header(from step 2) to take a count

    5. the countif values obtained in step4 are all summed up and thrown as the output

    this basic tutorial should be helpful in understanding how map/lambda thing works and all...