SOLVED

Sum up customer specific costs from another table

%3CLINGO-SUB%20id%3D%22lingo-sub-2935656%22%20slang%3D%22en-US%22%3ESum%20up%20customer%20specific%20costs%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2935656%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20trouble%20summing%20up%20customer%20specific%20costs%20from%20another%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20loads%20of%20columns%2C%20the%20revelent%20ones%20are%3A%20Order%20ID%2C%20Customer%20ID%20and%20Cost%3C%2FP%3E%3CP%3EThere%20can%20be%20multiple%20Order%20IDs%20per%20Customer%20ID%20and%20each%20Order%20has%20a%20Cost.%3C%2FP%3E%3CP%3EI%20have%20created%20another%20table%20with%20the%20Customer%20IDs%20listed.%20Now%20I%20need%20an%20array%20formula%20that%20sums%20up%20the%20Costs%20of%20all%20of%20the%20Order%20IDs%20for%20each%20individual%20Customer%20ID.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20this%20is%20possible%20with%20grouping%20Customer%20ID%20and%20aggregations%20for%20the%20sum%20of%20the%20Costs%20column.%3C%2FP%3E%3CP%3EI%20have%20to%20use%20array%20formulas.%20This%20is%20my%20best%20effort%20until%20now%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3DSUM(INDEX(Tabelle1!U2%3AU15%2CMATCH(1%2CIF(A2%3DTabelle1!F2%3AF15%2C1%2C0)%2C0)))%7D%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20there%20a%20few%20problems.%3C%2FP%3E%3CP%3E1.%20I%20don't%20know%20how%20to%20get%20the%20IF%20to%20return%20the%20row%20number%20for%20only%20the%20true%20values%3C%2FP%3E%3CP%3E2.%20I%20don't%20know%20if%20the%20INDEX%20can%20give%20back%20multiple%20values%20from%20different%20row%20numbers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20appreciate%20help.%3C%2FP%3E%3CP%3ERegards%20Tim%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(338).png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F324842i88D612B5068F22AA%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%20(338).png%22%20alt%3D%22Screenshot%20(338).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(339).png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F324843i7FB7FCD52E7016D5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%20(339).png%22%20alt%3D%22Screenshot%20(339).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2935656%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2935750%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20up%20customer%20specific%20costs%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2935750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1208076%22%20target%3D%22_blank%22%3E%40Mmrtlm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((A2%3DTabelle36!F2%3AF15)*Tabelle36!U2%3AU15)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20apply%20a%20formula%20like%20this%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2936209%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20up%20customer%20specific%20costs%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2936209%22%20slang%3D%22en-US%22%3ESadly%20I'm%20not%20allowed%20to%20publicise%20the%20excel%20file.%3CBR%20%2F%3EI'm%20running%20on%20the%20lates%20version%20of%20excel%20(2108)%2C%20so%20year%202021.%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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.