SOLVED

How to aggregate the drop down list items to get the category-wise sum?

%3CLINGO-SUB%20id%3D%22lingo-sub-1877713%22%20slang%3D%22en-US%22%3EHow%20to%20aggregate%20the%20drop%20down%20list%20items%20to%20get%20the%20category-wise%20sum%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1877713%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20file.%20I%20am%20trying%20to%20calculate%20the%20costs%20in%20terms%20of%20categories%20(services)%20from%20drop%20down%20lists.%26nbsp%3BSheet%203%20is%20the%20final%20sheet%20to%20show%20the%20calculated%20results%20based%20on%20the%20data%20from%20sheet%201%20and%202.%20Is%20there%20any%20way%20to%20combine%20the%20results%20of%20different%20categories%20in%20the%20final%20sheet%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20taking%20the%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3ETooba%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1877713%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1877828%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20aggregate%20the%20drop%20down%20list%20items%20to%20get%20the%20category-wise%20sum%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1877828%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F826924%22%20target%3D%22_blank%22%3E%40MTooba%3C%2FA%3E%26nbsp%3Bin%20the%20attached%20I%20did%20what%20I%20think%20you%20want%20in%202%20different%20ways%3A%3C%2FP%3E%3CP%3Ea)%20pivot%20table%20-%20this%20is%20powerful%20and%20flexible%20solution%3C%2FP%3E%3CP%3Eb)%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIF(Sheet2!F%3AF%2CC6%3AC10%2CSheet2!H%3AH)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eif%20the%20array%20formula%20above%20doesn't%20work%20then%20replace%20C6%3AC10%20with%20just%20C6%20and%20copy%20it%20down%20the%20column%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1877947%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20aggregate%20the%20drop%20down%20list%20items%20to%20get%20the%20category-wise%20sum%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1877947%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much.%20It%20is%20really%20helpful.%20The%20formula%20with%20C6%3AC10%20works%20well.%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi,

 

Please see the attached file. I am trying to calculate the costs in terms of categories (services) from drop down lists. Sheet 3 is the final sheet to show the calculated results based on the data from sheet 1 and 2. Is there any way to combine the results of different categories in the final sheet?

 

Thank you very much for taking the time.

 

Best regards,

Tooba

2 Replies
Highlighted
Best Response confirmed by MTooba (Occasional Contributor)
Solution

@MTooba in the attached I did what I think you want in 2 different ways:

a) pivot table - this is powerful and flexible solution

b) formula:

=SUMIF(Sheet2!F:F,C6:C10,Sheet2!H:H)

if the array formula above doesn't work then replace C6:C10 with just C6 and copy it down the column

Highlighted
Thank you very much. It is really helpful. The formula with C6:C10 works well.