Multiple Subtotals in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1450096%22%20slang%3D%22en-US%22%3EMultiple%20Subtotals%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1450096%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Afternoon%2C%3C%2FP%3E%3CP%3EIn%20my%20report%2C%20I%20need%20to%20subtotal%20column%20V%20to%20show%20the%20count%20of%20transaction%20per%20week.%20That%20I%20can%20do.%20But%20I%20also%20need%20to%20subtotal%20the%20value%20of%20the%20transactions.%20Can%20you%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ETony%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1450096%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%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-1450250%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Subtotals%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1450250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F693476%22%20target%3D%22_blank%22%3E%40Tony_McGraw%3C%2FA%3E%26nbsp%3BDon't%20think%20you%20can%20do%20this%20in%20one%20single%20action.%20Inserting%20subtotals%20forces%20you%20to%20select%20one%20type%20of%20operation%20e.g.%20COUNT%20or%20SUM%2C%20but%20you%20can%20do%20both.%20But%20if%20you%20select%20COUNT%20for%20both%20the%20transactionID%20column%20and%20the%20value%20column%20for%20each%20change%20in%20Date%2C%20you%20will%20generate%20subtotal%20formulae%20like%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUBTOTAL(3%2C%3CRANGE%3E)%3C%2FRANGE%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BThen%20select%20the%20value%20column%20and%20do%20a%20Find%26amp%3BReplace%2C%20find%20%3CFONT%20color%3D%22%23FF0000%22%3E(3%2C%3C%2FFONT%3E%20and%20replace%20it%20by%20%3CFONT%20color%3D%22%23FF0000%22%3E(9%2C%3CFONT%20color%3D%22%23000000%22%3E%26nbsp%3B.%20the%20formulae%20in%20the%20value%20column%20will%20become%26nbsp%3B%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUBTOTAL(9%2C%3CRANGE%3E)%3C%2FRANGE%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Good Afternoon,

In my report, I need to subtotal column V to show the count of transaction per week. That I can do. But I also need to subtotal the value of the transactions. Can you help?

 

Regards,

Tony

2 Replies
Highlighted

@Tony_McGraw Don't think you can do this in one single action. Inserting subtotals forces you to select one type of operation e.g. COUNT or SUM, but you can do both. But if you select COUNT for both the transactionID column and the value column for each change in Date, you will generate subtotal formulae like 

=SUBTOTAL(3,<range>)

 Then select the value column and do a Find&Replace, find (3, and replace it by (9, . the formulae in the value column will become 

=SUBTOTAL(9,<range>)

 

Highlighted

@Tony_McGraw 

Perhaps PivotTable could do the job