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.

What version of Excel are you using? – shrivallabha.redij Oct 26 '22 at 10:21 — shrivallabha.redij, Oct 26 '22 at 10:21
Excel 2021 (office 365) – TheDemonLord Oct 26 '22 at 10:28 — TheDemonLord, Oct 26 '22 at 10:28

Mayukh Bhattacharya · Accepted Answer · 2022-10-26T12:17:44.697

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

I've got a bit of experience with PowerQuery, so I feel very silly I didn't think of that (have used it with PowerBI) - however eventually got your query to work - the breakdown and step-by-step detail as to how the whole thing worked was super helpful for my troubleshooting it when it wasn't working (Had to re-arrange columns, delete unused columns, remove blank values and all sorts of other fun and games) - eventually got the output I needed - thanks heaps. — TheDemonLord, Oct 26 '22 at 19:10
@TheDemonLord glad to know that, sounds really good, that was helpful. — Mayukh Bhattacharya, Oct 26 '22 at 19:20

score 0 · Answer 2 · answered Oct 26 '22 at 10:32

There are more than one way to achieve this. If you have Office 365, then you can try following. I have set it up on one sheet as below.

Formula in Blue Cell G2 is

=UNIQUE($B$2:$B$7,FALSE)

Formula in Gold Cell H2 is

=SUM(LOOKUP(FILTER($A$2:$A$7,$B$2:$B$7=$G2),$D$2:$D$3,$E$2:$E$3))

You will have to adopt this to suit your sheet/data structure.

Summing, Lookups and multiple sheets in Excel

2 Answers2