SOLVED

# Sum up customer specific costs from another table

Occasional Contributor

# 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

# Re: Sum up customer specific costs from another table

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 (Occasional Contributor)
Solution

# Re: Sum up customer specific costs from another table

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

I would apply a formula like this one.

# Re: Sum up customer specific costs from another table

Sadly I'm not allowed to publicise the excel file.
I'm running on the lates version of excel (2108), so year 2021.

# Re: Sum up customer specific costs from another table

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.

# Re: Sum up customer specific costs from another table

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.