How to Summary Qty vs Price

stormania

Member
Dear Excell Masters

Kindly help how summaries beetwen 2 different data (price & qty) then recap in one group as per attach file

Attachments

• 18.2 KB Views: 5

AlanSidman

Well-Known Member
Using Power Query, bring each table into the PQ editor. Merge (Left Join) the two tables. Here is the Mcode for the Merge

Code:
``````let
Source = Table.NestedJoin(Table1, {"Kode"}, Table3, {"Kode"}, "Table3", JoinKind.LeftOuter),
#"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Qty in pcs"}, {"Qty in pcs"}),
#"Inserted Multiplication" = Table.AddColumn(#"Expanded Table3", "Multiplication", each [#"Price/pcs"] * [Qty in pcs], Int64.Type),
#"Grouped Rows" = Table.Group(#"Inserted Multiplication", {"Group"}, {{"Quantity", each List.Sum([Qty in pcs]), type nullable number}, {"Price", each List.Sum([#"Price/pcs"]), type nullable number}})
in
#"Grouped Rows"``````
Data Range
 A​ B​ C​ 1​ Group​ Quantity​ Price​ 2​ Fruit​ 80​ 134​ 3​ Vegetable​ 69​ 69​

Attachments

• 36.7 KB Views: 6

stormania

Member
Thanks for solution but i am a new..maybe any coomons formula?

stormania

Member
the result that i mean is total price after one by one qty of products time each price
Maybe any coomon formula solution?

bosco_yip

Excel Ninja
1] In "Total Recap Price" B5, formula copied down :

=SUMPRODUCT((price!\$C\$5:\$C\$10=A5)*price!\$D\$5:\$D\$10*qty!\$D\$5:\$D\$10)

2] In "Total Recap Quantity" F5, formula copied down :

=SUMIF(qty!C:C,E5,qty!D:D)

deciog

Active Member
Check if this is how you wish

Decio

Attachments

• 13 KB Views: 2

stormania

Member
1] In "Total Recap Price" B5, formula copied down :

=SUMPRODUCT((price!\$C\$5:\$C\$10=A5)*price!\$D\$5:\$D\$10*qty!\$D\$5:\$D\$10)

2] In "Total Recap Quantity" F5, formula copied down :

=SUMIF(qty!C:C,E5,qty!D:D)

View attachment 70991
GREATTT...AMAZING ...Thank you so much..

stormania

Member
Check if this is how you wish

Decio
thank for support..but formula and result that i mean ..as bosco-yip (above) solutions

Peter Bartholomew

Well-Known Member
I may have missed the point, but here are a couple of variations upon a formula approach.
@deciog's products are harder to achieve with dynamic arrays because of the limitations of SUMIFS.

Attachments

• 24 KB Views: 4