excelexcel-formulasumifs

Sumifs rows based on header

Is there a way to do sumifs in a range which criteria is based on several headers without using a helper column?

enter image description here

In the table A1:D21, I wanted to create a summary below it (A23:D28) showing the total sum of each Team Leaders of their total Inbound and Outbound, and showing the grand total.

Normally what I would do is add a helper column adding the name of team leaders beside those agents to simply do sumifs. Is it possible to do this without the help of a helper column?

Update Added Another Sample Close To What I Am Currently Working On

enter image description here

All Team Leaders and Agents are unique, I just duplicate the tables as the one I am working on has a lot of set of tables that are placed like this.

Name Entered Calls Accepted Calls Accept % Name Entered Calls Accepted Calls Accept % Name Entered Calls Accepted Calls Accept %
Team Leader A Team Leader A Team Leader A
Agent 1 100 50 50% Agent 1 100 50 50% Agent 1 100 50 50%
Agent 2 100 60 60% Agent 2 100 60 60% Agent 2 100 60 60%
Agent 3 100 70 70% Agent 3 100 70 70% Agent 3 100 70 70%
Agent 4 100 80 80% Agent 4 100 80 80% Agent 4 100 80 80%
Agent 5 100 90 90% Agent 5 100 90 90% Agent 5 100 90 90%
Totals 500 350 70% Totals 500 350 70% Totals 500 350 70%
Name Entered Calls Accepted Calls Accept % Name Entered Calls Accepted Calls Accept % Name Entered Calls Accepted Calls Accept %
Team Leader B Team Leader B Team Leader B
Agent 6 100 50 50% Agent 6 100 50 50% Agent 6 100 50 50%
Agent 7 100 60 60% Agent 7 100 60 60% Agent 7 100 60 60%
Agent 8 100 70 70% Agent 8 100 70 70% Agent 8 100 70 70%
Agent 9 100 80 80% Agent 9 100 80 80% Agent 9 100 80 80%
Agent 10 100 90 90% Agent 10 100 90 90% Agent 10 100 90 90%
Totals 500 350 70% Totals 500 350 70% Totals 500 350 70%
Name Entered Calls Accepted Calls Accept % Name Entered Calls Accepted Calls Accept % Name Entered Calls Accepted Calls Accept %
Team Leader C Team Leader C Team Leader C
Agent 11 100 50 50% Agent 11 100 50 50% Agent 11 100 50 50%
Agent 12 100 60 60% Agent 12 100 60 60% Agent 12 100 60 60%
Agent 13 100 70 70% Agent 13 100 70 70% Agent 13 100 70 70%
Agent 14 100 80 80% Agent 14 100 80 80% Agent 14 100 80 80%
Agent 15 100 90 90% Agent 15 100 90 90% Agent 15 100 90 90%
Totals 500 350 70% Totals 500 350 70% Totals 500 350 70%
Name Entered Calls Accepted Calls Accept % Name Entered Calls Accepted Calls Accept % Name Entered Calls Accepted Calls Accept %
Team Leader D Team Leader D Team Leader D
Agent 16 100 50 50% Agent 16 100 50 50% Agent 16 100 50 50%
Agent 17 100 60 60% Agent 17 100 60 60% Agent 17 100 60 60%
Agent 18 100 70 70% Agent 18 100 70 70% Agent 18 100 70 70%
Agent 19 100 80 80% Agent 19 100 80 80% Agent 19 100 80 80%
Agent 20 100 90 90% Agent 20 100 90 90% Agent 20 100 90 90%
Totals 500 350 70% Totals 500 350 70% Totals 500 350 70%
Entered Calls Accepted Calls Accept % Entered Calls Accepted Calls Accept % Entered Calls Accepted Calls Accept %
Team Leader A 500 350 70% Team Leader A 500 350 70% Team Leader A 500 350 70%
Team Leader B 500 350 70% Team Leader B 500 350 70% Team Leader B 500 350 70%
Team Leader C 500 350 70% Team Leader C 500 350 70% Team Leader C 500 350 70%
Team Leader D 500 350 70% Team Leader D 500 350 70% Team Leader D 500 350 70%
Totals 2000 1400 70% Totals 2000 1400 70% Totals 2000 1400 70%
Solution

  • Here is one way of doing this:

    enter image description here

    =LET(
     _Filler, SCAN(,IF(D1:D21="Total",A1:A21,""),LAMBDA(x,y,IF(y="",x,y))),
     _Output, FILTER(HSTACK(_Filler,B1:D21),TEXTAFTER(_Filler&"|"&A1:A21,"|")="Totals"),
     VSTACK({"","Inbound","Outbound","Total"},_Output, HSTACK("",MMULT({1,1,1},DROP(_Output,,1)))))

    Another Method without using LAMBDA() helper functions, the following solution may or may not work with the actuals, but here is what I have assumed as per the given data in the OP, if all the agents are same for each team leaders then we can wipe them out using UNIQUE() to grab only the distinct ones, yes it is to be noted again, that i am deliberately doing this based on the given data that the agents will not be different and then with manipulation with DROP() , TAKE() and FILTER() function we can achieve the desired output. All these depends, again this is completely based on the given sample data and pattern based.:

    enter image description here

    =LET(
     _Data, A1:D21,
     _Teams, UNIQUE(TAKE(_Data,,1),,1),
     _Vals, DROP(FILTER(_Data, TAKE(_Data,,1)="Totals"),,1),
     _Combined, HSTACK(VSTACK(_Teams,""), VSTACK(_Vals, MMULT({1,1,1}, _Vals))),
     VSTACK(HSTACK("",DROP(TAKE(_Data,1),,1)), _Combined))

    EDIT: Updated solution based on the edit with new sample data posted by OP at 2024-05-01 21:14:40Z

    enter image description here

    =LET(
     _Data, A1:D32,
     _TeamLeaders, TAKE(FILTER(_Data, INDEX(_Data,,2)=""),,1),
     _Vals, DROP(FILTER(_Data, TAKE(_Data,,1)="Totals"),,1),
     _Totals, MMULT({1,1,1,1}, TAKE(_Vals,,2)),
     _Bottom, HSTACK("Totals",_Totals, TAKE(_Totals,,-1)/TAKE(_Totals,,1)),
     _Middle, HSTACK(_TeamLeaders,_Vals),
     _Top, HSTACK("",DROP(TAKE(_Data,1),,1)),
     VSTACK(_Top, _Middle, _Bottom))

    Suggestion: Put the following formula in the name manager and define as SUMMARY

    =LAMBDA(array,
   LET(
       _Data, array,
       _TeamLeaders, TAKE(FILTER(_Data, INDEX(_Data,,2)=""),,1),
       _Vals, DROP(FILTER(_Data, TAKE(_Data,,1)="Totals"),,1),
       _Totals, MMULT({1,1,1,1}, TAKE(_Vals,,2)),
       _Bottom, HSTACK("Totals",_Totals, TAKE(_Totals,,-1)/TAKE(_Totals,,1)),
       _Middle, HSTACK(_TeamLeaders,_Vals),
       _Top, HSTACK("",DROP(TAKE(_Data,1),,1)),
       VSTACK(_Top, _Middle, _Bottom)))

    And now use the following formula below each table as :

    enter image description here

    =SUMMARY(A1:D32)

    =SUMMARY(F1:I32)

    =SUMMARY(K1:N32)