Forum Discussion
Subtotals after each group
Hi all,
I am new to this community and I haven't really worked with excel in terms of creating reports. I have been using Power BI but i am not able to recreate the below.
I have the below report in Business Objects and would like to replicate this in excel. In yellow is what i would like to show. So the grouping is based on W litres and then the collection litres are summed. Is this possible to do in excel?
You can get subtotals on one line by creating them in one go, then altering the formulas.
But it won't work for the DATETIME_CUST column since it contains text values that Excel doesn't recognize as valid dates and times. It will return 0 as maximum. You'd have to change the values to real date/time values for the SUBTOTAL formulas to work.
See the attached version.
6 Replies
Click anywhere in the table.
On the Data tab of the ribbon, in the Outline group, click Subtotal.
Select W_LITRES in the 'At each change in:' dropdown.
Tick the check box for COLLECTION_LITRES in the 'Add totals to;' dropdown, and clear the check boxes for columns you don't want to total.
Click OK.
HansVogelaar thanks for that! Is there anyway to have the subtotals on the same line? I want to have max of two columns then a sum of another column, but it is creating two separate lines?
Can you explain in more detail what you want to do, perhaps attach a sample workbook?
