Forum Discussion
danielting1
Mar 24, 2021Copper Contributor
EXCEL: Sum of the multiplication between two tables with the criteria matched
I have this issue, where I need to multiply between two tables with the same header. For simplicity sake, let's just put 3 products - Product A, B, C. I have month-end data for both month-...
danielting1
Mar 26, 2021Copper Contributor
SergeiBaklanI get this, thanks!
But assuming the table is not sorted accordingly: e.g.
Price table is sorted like it's Product A, Product C, Product B; whilst
Quantity table is sorted like it's Product A, Product B, Product C
The solution proposed would not work right?
So the best way is to make sure both rows / column headers are sorted, and there isn't an alternative to it?
SergeiBaklan
Mar 26, 2021Diamond Contributor
If columns are in different order you may use nested XLOOKUP to take proper columns
with
=SUM(
XLOOKUP(Price[[#Headers],[B]:[A]],
Qty[[#Headers],[A]:[C]],
XLOOKUP(G3,Qty[Date],Qty[[A]:[C]]))*
XLOOKUP(G3,Price[Date],Price[[B]:[A]])
)