Forum Discussion

Mmrtlm's avatar
Mmrtlm
Copper Contributor
Nov 07, 2021
Solved

Sum up customer specific costs from another table

Hey there,

 

I'm having trouble summing up customer specific costs from another table.

 

I have a table with loads of columns, the revelent ones are: Order ID, Customer ID and Cost

There can be multiple Order IDs per Customer ID and each Order has a Cost.

I have created another table with the Customer IDs listed. Now I need an array formula that sums up the Costs of all of the Order IDs for each individual Customer ID. 

 

I know this is possible with grouping Customer ID and aggregations for the sum of the Costs column.

I have to use array formulas. This is my best effort until now:

 

{=SUM(INDEX(Tabelle1!U2:U15,MATCH(1,IF(A2=Tabelle1!F2:F15,1,0),0)))} 

 

I know there a few problems.

1. I don't know how to get the IF to return the row number for only the true values

2. I don't know if the INDEX can give back multiple values from different row numbers

 

I would appreciate help.

Regards Tim

 

5 Replies

  • Mmrtlm 

    =SUMPRODUCT((A2=Tabelle36!F2:F15)*Tabelle36!U2:U15)

     

    I would apply a formula like this one.

    • Mmrtlm's avatar
      Mmrtlm
      Copper Contributor
      That worked like a charm, thank you very much.
      Is it possible to modify the SUMPRODUCT to something like SUM(PRODUCT()), so that part of it returns an array? Sadly thats necessary for my assignment.
  • Hi there,
    It would be better to have excel file itself.
    What I would like to ask is related to Excel version which you use.
    If this version is 2016 and previous ones, you may use CSE (Control+Shift+Enter) combination for Array Formulas.
    • Mmrtlm's avatar
      Mmrtlm
      Copper Contributor
      Sadly I'm not allowed to publicise the excel file.
      I'm running on the lates version of excel (2108), so year 2021.

Resources