Sort & Sum Through Excel Formula

[kme]

Excel formula Required

Hear attached Data Sheet a Group Company Sales and Profit Under Different branches .

We Need sort Through Excel Formula Based On each employee name. Sample Answer Given In 2nd Sheet.

Sales and Profit amount Should be the sum of the division under Salesman

 

[GraH - Guido]

School assignment?
Did you try yourself anything at all?

 

[p45cal]

Pivot:

 

[kme]

Pivot Table Is Ok!. Can I know formula

 

[p45cal]

=GROUPBY(HSTACK('Sales Data'!$K$2:$K$163,'Sales Data'!$A$2:$A$163,'Sales Data'!$J$2:$J$163,'Sales Data'!$L$2:$L$163),'Sales Data'!$G$2:$H$163,SUM,3,0)

 

[kme]

=GROUPBY(HSTACK('Sales Data'!$K$2:$K$163,'Sales Data'!$A$2:$A$163,'Sales Data'!$J$2:$J$163,'Sales Data'!$L$2:$L$163),'Sales Data'!$G$2:$H$163,SUM,3,0)

extremely thankful . really appreciate

 

[AlanSidman]

An alternative with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Market", "Employee name", "Branch Name"}, {{"Total Sales", each List.Sum([Sales Amount]), type number}, {"Total Profit", each List.Sum([Profit]), type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Market", "Branch Name", "Employee name", "Total Sales", "Total Profit"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Branch Name", Order.Ascending}, {"Market", Order.Ascending}})
in
    #"Sorted Rows"