SOLVED

Sum up customer specific costs from another table

Copper Contributor

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

 

Screenshot (338).png

Screenshot (339).png

5 Replies
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.
best response confirmed by Mmrtlm (Copper Contributor)
Solution

@Mmrtlm 

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

 

I would apply a formula like this one.

Sadly I'm not allowed to publicise the excel file.
I'm running on the lates version of excel (2108), so year 2021.
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.

@Mmrtlm 

You can enter formula

=(A2=Tabelle36!K2:K15)*Tabelle36!U2:U15

in your excel file to retrieve an array returning value for the selected product as shown in attachment Sumproductarray.

1 best response

Accepted Solutions
best response confirmed by Mmrtlm (Copper Contributor)
Solution

@Mmrtlm 

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

 

I would apply a formula like this one.

View solution in original post