• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Thanks in advance
 

Attachments

  • RECAP Price vs Qty.xlsx
    18.2 KB · Views: 5
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

  • RECAP Price vs Qty.xlsx
    36.7 KB · Views: 7
the result that i mean is total price after one by one qty of products time each price
Maybe any coomon formula solution?
 
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)

70991
 
Check if this is how you wish

Decio
 

Attachments

  • RECAP Price vs Qty.xlsx
    13 KB · Views: 2
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

  • RECAP Price vs Qty.xlsx
    24 KB · Views: 4
Back
Top