Forum Discussion
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
- OliverScheurichGold Contributor
- MmrtlmCopper ContributorThat 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.- OliverScheurichGold Contributor
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.
- 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.- MmrtlmCopper ContributorSadly I'm not allowed to publicise the excel file.
I'm running on the lates version of excel (2108), so year 2021.