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.
CodePudding user response：
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.
CodePudding user response：
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:
• _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.