Nov 07 2021 02:35 AM
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
Nov 07 2021 02:43 AM
Nov 07 2021 03:17 AM
SolutionNov 07 2021 07:08 AM
Nov 07 2021 07:10 AM
Nov 07 2021 07:54 AM
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.
Nov 07 2021 03:17 AM
Solution=SUMPRODUCT((A2=Tabelle36!F2:F15)*Tabelle36!U2:U15)
I would apply a formula like this one.