Summing, Lookups and multiple sheets in Excel

Question

I'm not a native Excel user (much more of a SQL man) and I have the following scenario that is doing my head in. Mainly because I'm sure it's relatively simple, but because I'm not super-familiar with all the advanced functions of Excel.

I have a 2 sheets in question.

Sheet One has the following columns:

SKU	Price
1234	$10
1235	$20

Sheet Two has the following Columns:

SKU	Business Unit
1234	BU1
1235	BU1
1234	BU1
1234	BU2
1234	BU2
1234	BU2

And I have the following Formula:

=SUMIF('Sheet1'[SKU], VLOOKUP($F$2, sheet2, 2, FALSE), 'Sheet1'[Price])

(Which admittedly is copy-pasta from the Internets and then I've tried to mash together to get it to do what I want)

What I am trying to do is grouping by Business Unit, look up the SKUs and multiply the total, based on Business Unit by the Price - so it would look like the following:

Business Unit	Total Value
BU1	$40
BU2	$30

And my limitations in Excel are causing my hair to fall out as I bang my head against my keyboard - as I'm sure it's relatively simple - but I'm missing something key.

Answer 1

You may try as shown in below as well,

---

• Formula used in cell G2

=LET(_merge,DROP(HSTACK(A3:B8,XLOOKUP(A3:A8,D3:D4,E3:E4)),,1),
_uBUnit,UNIQUE(INDEX(_merge,,1)),
_tValue,BYROW(_uBUnit,LAMBDA(x,SUM(INDEX(_merge,,2)*(INDEX(_merge,,1)=x)))),
VSTACK({"Business Unit","Total Value"},HSTACK(_uBUnit,_tValue)))

---

Notes: Break-down & Explanation Of Each.

• _merge --> Returns both the tables as combined after looking the price for each SKU and then excludes the SKU from the array, only keeping the one required as output, i.e., Business Unit & Price

XLOOKUP() --> Looks Up On SKU To Return The Price.

HSTACK() --> Used To Combine Both The Arrays.

=HSTACK(A3:B8,XLOOKUP(A3:A8,D3:D4,E3:E4))

Using DROP() --> To Exclude The SKU Col.

DROP(HSTACK(A3:B8,XLOOKUP(A3:A8,D3:D4,E3:E4)),,1)

---

• _uBUnit --> Returns the unique value of each Business Unit.

UNIQUE(INDEX(_merge,,1))

---

• _tValue --> Returns the Total Values of each Business Unit

BYROW(_uBUnit,LAMBDA(x,SUM(INDEX(_merge,,2)*(INDEX(_merge,,1)=x))))

---

• Lastly we are packing the whole thing, within a VSTACK() & HSTACK() to get the required output.

VSTACK({"Business Unit","Total Value"},HSTACK(_uBUnit,_tValue))

---

Please suit the data ranges accordingly with your data.

---

You can also perform such tasks quite easily using Power Query as well:

To accomplish this task using Power Query please follow the steps,

---

• Select some cell in your Data Table,

• Data Tab => Get&Transform => From Table/Range,

• When the PQ Editor opens: Home => Advanced Editor,

• Make note of all the 2 Tables Names,

• Paste the M Code below in place of what you see.

• And refer the notes

---

let
    //Source Table -- SKUtbl
    SourceOne = Excel.CurrentWorkbook(){[Name="SKUtbl"]}[Content],
    DataTypeSourceOne = Table.TransformColumnTypes(SourceOne,{{"SKU", Int64.Type}, {"Business Unit", type text}}),

    //Source Table -- Pricetbl
    SourceTwo = Excel.CurrentWorkbook(){[Name="Pricetbl"]}[Content],
    DataTypeSourceTwo = Table.TransformColumnTypes(SourceTwo,{{"SKU", Int64.Type}, {"Price", Int64.Type}}),

    //Merging Both Tables
    MergeTables = Table.NestedJoin(DataTypeSourceOne, {"SKU"}, DataTypeSourceTwo, {"SKU"}, "Pricetbl", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(MergeTables, "Pricetbl", {"Price"}, {"Price"}),
    
    //Removing the SKU Column
    #"Removed Columns" = Table.RemoveColumns(Expanded,{"SKU"}),
    
    //Grouping By Business Unit
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Business Unit"}, {{"Total Value", each List.Sum([Price]), type nullable number}})
in
    #"Grouped Rows"

---

---

• Change the Table name as BusinessUnittbl before importing it back into Excel.

• When importing, you can either select Existing Sheet with the cell reference you want to place the table or you can simply click on NewSheet