Forum Discussion

danielting1's avatar
danielting1
Copper Contributor
Mar 24, 2021

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

excel
Formulas and Functions

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 24, 2021

    danielting1 

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

     

    • danielting1's avatar
      danielting1
      Copper Contributor
      Mar 26, 2021

      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's avatar
        SergeiBaklan
        Diamond Contributor
        Mar 26, 2021

        danielting1 

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

Resources