Mar 24 2021 02:27 AM - edited Mar 24 2021 02:27 AM
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-end price and quantity at hand in two separate tables (however, when extracted they are not sorted in order).
Is there a way I can get the total month end value in dollars (i.e. Quantity A * Price A + Quantity B * Price B + Quantity C * Price C + ...)?
Or the only way is to sort both tables each time and use SUMPRODUCT?
Mar 24 2021 03:07 AM
If dates are in different order first you need return dates
e.g. as
=SORT(Qty[Date])
and at the right calculate price for each date
=SUM(XLOOKUP(G3,Qty[Date],Qty[[A]:[C]])*
XLOOKUP(G3,Price[Date],Price[[A]:[C]]))
Mar 26 2021 04:50 AM
@Sergei BaklanI 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?
Mar 26 2021 03:46 PM
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]])
)