EXCEL: Sum of the multiplication between two tables with the criteria matched

%3CLINGO-SUB%20id%3D%22lingo-sub-2231970%22%20slang%3D%22en-US%22%3ESum%20of%20the%20multiplication%20between%20two%20tables%20with%20the%20criteria%20matched%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2231970%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22danielt805_1-1616577661389.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266606iC145DE87F08F2C26%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22danielt805_1-1616577661389.png%22%20alt%3D%22danielt805_1-1616577661389.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20this%20issue%2C%20where%20I%20need%20to%20multiply%20between%20two%20tables%20with%20the%20same%20header.%3C%2FP%3E%3CP%3EFor%20simplicity%20sake%2C%20let's%20just%20put%203%20products%20-%20Product%20A%2C%20B%2C%20C.%3C%2FP%3E%3CP%3EI%20have%20month-end%20data%20for%20both%20month-end%20price%20and%20quantity%20at%20hand%20in%20two%20separate%20tables%20(however%2C%20when%20extracted%20they%20are%20not%20sorted%20in%20order).%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20I%20can%20get%20the%20total%20month%20end%20value%20in%20dollars%20(i.e.%20Quantity%20A%20*%20Price%20A%20%2B%20Quantity%20B%20*%20Price%20B%20%2B%20Quantity%20C%20*%20Price%20C%20%2B%20...)%3F%3C%2FP%3E%3CP%3EOr%20the%20only%20way%20is%20to%20sort%20both%20tables%20each%20time%20and%20use%20SUMPRODUCT%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2231970%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2232041%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%3A%20Sum%20of%20the%20multiplication%20between%20two%20tables%20with%20the%20criteria%20matched%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2232041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1006993%22%20target%3D%22_blank%22%3E%40danielting1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20dates%20are%20in%20different%20order%20first%20you%20need%20return%20dates%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20507px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266616iBC61E6731415CD4F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ee.g.%20as%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSORT(Qty%5BDate%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20at%20the%20right%20calculate%20price%20for%20each%20date%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(XLOOKUP(G3%2CQty%5BDate%5D%2CQty%5B%5BA%5D%3A%5BC%5D%5D)*%0A%20%20%20%20%20XLOOKUP(G3%2CPrice%5BDate%5D%2CPrice%5B%5BA%5D%3A%5BC%5D%5D))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

danielt805_1-1616577661389.png

 

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?

3 Replies

@danielting1 

If dates are in different order first you need return dates

image.png

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]]))

 

@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?

 

@danielting1 

If columns are in different order you may use nested XLOOKUP to take proper columns

image.png

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]])
)