Spreadsheet: 819134d1677235606-formula-to-grouping-data-subtotal-each-depart-and-grand-total_3
Sheet: Sheet1

 | COMPANY |  | DEPT |  | NAME |  |  |  | Name | DEPT | COMPANY
 | XYZ. Inc |  | EXTERNAL RELATION |  | MARIYONO |  | 100
 | ABC. Inc |  | FINANCE & ACCOUNTING |  | NAWIL |  | 150 |  |  |  |  |  | ABC. Inc |  |  |  |  | =LET(Comp,B3:B2000,Dept,[NAME]:D2000,Name,F3:F2000,CComp,ISNUMBER(SEARCH(L3,Comp)),CDept,ISNUMBER(SEARCH(K3,Dept)),CName,ISNUMBER(SEARCH(J3,Name)),f,UNIQUE(FILTER(HSTACK(Dept,Name,Comp),CComp*CDept*CName)),DROP(REDUCE("",UNIQUE(INDEX(f,,3)),LAMBDA(x,y,IFNA(HSTACK(x,LET(a,FILTER(f,INDEX(f,,3)=y),b,UNIQUE(INDEX(a,,1)),c,b&"|zzzz",d,MAP(SEQUENCE(ROWS(b)),LAMBDA(s,SUM(--(INDEX(a,,1)=INDEX(b,s,1))))),e,SORT(VSTACK(a,IFERROR(HSTACK(c,"",d),""))),g,VSTACK(e,HSTACK("Grand Total","",SUM(d))),h,MAP(TAKE(g,,1),LAMBDA(s,[NAME],[NAME]))
 | XYZ. Inc |  | GENERAL SERVICES |  | WAHYUDIANTO |  | 200 |  | EXTERNAL RELATION | MARIYONO | XYZ. Inc | 100 | EXTERNAL RELATION | AHMAD | ABC. Inc | 250
 | XYZ. Inc |  | HUMAN RESOURCES |  | MUTTAQIN |  | 250 |  | EXTERNAL RELATION | NUR QAYIN | XYZ. Inc | 150 | Sub Total |  |  | 1
 | XYZ. Inc |  | MAINTENANCE & CONSTRUCTION |  | WILSON |  | 150 |  | Sub Total |  |  | 2 | FINANCE & ACCOUNTING | ADI PUTRANTO | ABC. Inc | 150
 | XYZ. Inc |  | MINE GEOLOGY & PLANNING |  | FAHRIAL |  | 100 |  | FINANCE & ACCOUNTING | [NAME] | XYZ. Inc | 100 | FINANCE & ACCOUNTING | NAWIL | ABC. Inc | 150
 | XYZ. Inc |  | MINE GEOLOGY & PLANNING |  | MOECHAMMAD |  | 150 |  | Sub Total |  |  | 1 | Sub Total |  |  | 2
 | XYZ. Inc |  | OPERATION |  | SELAMAT |  | 200 |  | GENERAL SERVICES | WAHYUDI | XYZ. Inc | 150 | GENERAL SERVICES | ALFIAN | ABC. Inc | 100
 | XYZ. Inc |  | QSE |  | ARAFIK |  | 250 |  | GENERAL SERVICES | WAHYUDIANTO | XYZ. Inc | 200 | Sub Total |  |  | 1
 | XYZ. Inc |  | EXTERNAL RELATION |  | NUR QAYIN |  | 150 |  | Sub Total |  |  | 2 | HUMAN RESOURCES | SUGENG | ABC. Inc | 150
 | XYZ. Inc |  | FINANCE & ACCOUNTING |  | [NAME] |  | 100 |  | HUMAN RESOURCES | [NAME] | XYZ. Inc | 200 | Sub Total |  |  | 1
 | XYZ. Inc |  | GENERAL SERVICES |  | WAHYUDI |  | 150 |  | HUMAN RESOURCES | MUTTAQIN | XYZ. Inc | 250 | MAINTENANCE & CONSTRUCTION | TRIYANTO | ABC. Inc | 200
 | XYZ. Inc |  | HUMAN RESOURCES |  | [NAME] |  | 200 |  | Sub Total |  |  | 2 | Sub Total |  |  | 1
 | XYZ. Inc |  | MAINTENANCE & CONSTRUCTION |  | ARIS |  | 250 |  | MAINTENANCE & CONSTRUCTION | ARIS | XYZ. Inc | 250 | MINE GEOLOGY & PLANNING | IWAN | ABC. Inc | 250
 | XYZ. Inc |  | MINE GEOLOGY & PLANNING |  | MOHAMAD |  | 150 |  | MAINTENANCE & CONSTRUCTION | WILSON | XYZ. Inc | 150 | MINE GEOLOGY & PLANNING | [NAME] | ABC. Inc | 100
 | ABC. Inc |  | MINE GEOLOGY & PLANNING |  | [NAME] |  | 100 |  | Sub Total |  |  | 2 | Sub Total |  |  | 2
 | ABC. Inc |  | OPERATION |  | LIN SLAMET |  | 150 |  | MINE GEOLOGY & PLANNING | FAHRIAL | XYZ. Inc | 100 | OPERATION | ABI HAIR | ABC. Inc | 100
 | ABC. Inc |  | QSE |  | BAHAR |  | 200 |  | MINE GEOLOGY & PLANNING | MOECHAMMAD | XYZ. Inc | 150 | OPERATION | LIN SLAMET | ABC. Inc | 150
 | ABC. Inc |  | EXTERNAL RELATION |  | AHMAD |  | 250 |  | MINE GEOLOGY & PLANNING | MOHAMAD | XYZ. Inc | 150 | OPERATION | RISTANTO | ABC. Inc | 150
 | ABC. Inc |  | FINANCE & ACCOUNTING |  | ADI PUTRANTO |  | 150 |  | Sub Total |  |  | 3 | Sub Total |  |  | 3
 | ABC. Inc |  | GENERAL SERVICES |  | ALFIAN |  | 100 |  | OPERATION | SELAMAT | XYZ. Inc | 200 | QSE | ANDI YANTO | ABC. Inc | 150
 | ABC. Inc |  | HUMAN RESOURCES |  | SUGENG |  | 150 |  | Sub Total |  |  | 1 | QSE | BAHAR | ABC. Inc | 200
 | ABC. Inc |  | MAINTENANCE & CONSTRUCTION |  | TRIYANTO |  | 200 |  | QSE | ARAFIK | XYZ. Inc | 250 | Sub Total |  |  | 2
 | ABC. Inc |  | MINE GEOLOGY & PLANNING |  | IWAN |  | 250 |  | Sub Total |  |  | 1 | Grand Total |  |  | 2100
 | ABC. Inc |  | OPERATION |  | RISTANTO |  | 150 |  | Grand Total |  |  | 2400
 | ABC. Inc |  | OPERATION |  | ABI HAIR |  | 100
 | ABC. Inc |  | QSE |  | ANDI YANTO |  | 150 |  | EXPECTED ANSWER / RESULT
 |  |  |  |  |  |  |  |  | XYZ. Inc |  |  | ABC. Inc
 |  |  |  |  |  |  |  |  | EXTERNAL RELATION | MARIYONO | XYZ. Inc | 100 | EXTERNAL RELATION | AHMAD | ABC. Inc | 250
 |  |  |  |  |  |  |  |  | EXTERNAL RELATION | NUR QAYIN | XYZ. Inc | 150 | Sub Total
 |  |  |  |  |  |  |  |  | Sub Total |  |  |  | FINANCE & ACCOUNTING | ADI PUTRANTO | ABC. Inc | 150
 |  |  |  |  |  |  |  |  | FINANCE & ACCOUNTING | [NAME] | XYZ. Inc | 100 | FINANCE & ACCOUNTING | NAWIL | ABC. Inc | 150
 |  |  |  |  |  |  |  |  | Sub Total |  |  |  | Sub Total
 |  |  |  |  |  |  |  |  | GENERAL SERVICES | WAHYUDI | XYZ. Inc | 150 | GENERAL SERVICES | ALFIAN | ABC. Inc | 100
 |  |  |  |  |  |  |  |  | GENERAL SERVICES | WAHYUDIANTO | XYZ. Inc | 200 | Sub Total
 |  |  |  |  |  |  |  |  | Sub Total |  |  |  | HUMAN RESOURCES | SUGENG | ABC. Inc | 150
 |  |  |  |  |  |  |  |  | HUMAN RESOURCES | [NAME] | XYZ. Inc | 200 | Sub Total
 |  |  |  |  |  |  |  |  | HUMAN RESOURCES | MUTTAQIN | XYZ. Inc | 250 | MAINTENANCE & CONSTRUCTION | TRIYANTO | ABC. Inc | 200
 |  |  |  |  |  |  |  |  | Sub Total |  |  |  | Sub Total
 |  |  |  |  |  |  |  |  | MAINTENANCE & CONSTRUCTION | ARIS | XYZ. Inc | 250 | MINE GEOLOGY & PLANNING | IWAN | ABC. Inc | 250
 |  |  |  |  |  |  |  |  | MAINTENANCE & CONSTRUCTION | WILSON | XYZ. Inc | 150 | MINE GEOLOGY & PLANNING | [NAME] | ABC. Inc | 100
 |  |  |  |  |  |  |  |  | Sub Total |  |  |  | Sub Total
 |  |  |  |  |  |  |  |  | MINE GEOLOGY & PLANNING | FAHRIAL | XYZ. Inc | 100 | OPERATION | ABI HAIR | ABC. Inc | 100
 |  |  |  |  |  |  |  |  | MINE GEOLOGY & PLANNING | MOECHAMMAD | XYZ. Inc | 150 | OPERATION | LIN SLAMET | ABC. Inc | 150
 |  |  |  |  |  |  |  |  | MINE GEOLOGY & PLANNING | MOHAMAD | XYZ. Inc | 150 | OPERATION | RISTANTO | ABC. Inc | 150
 |  |  |  |  |  |  |  |  | Sub Total |  |  |  | Sub Total
 |  |  |  |  |  |  |  |  | OPERATION | SELAMAT | XYZ. Inc | 200 | QSE | ANDI YANTO | ABC. Inc | 150
 |  |  |  |  |  |  |  |  | Sub Total |  |  |  | QSE | BAHAR | ABC. Inc | 200
 |  |  |  |  |  |  |  |  | QSE | ARAFIK | XYZ. Inc | 250 | Sub Total
 |  |  |  |  |  |  |  |  | Sub Total |  |  |  | Grand Total |  |  | 2100
 |  |  |  |  |  |  |  |  | Grand Total |  |  | 2400 | Grand Total All |  |  | 4500 | New Request Expected result