Dec 16 2020 08:18 AM
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?
Dec 16 2020 08:38 AM
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.
Dec 22 2020 04:29 AM
@Hans Vogelaar 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?
Dec 22 2020 04:38 AM
Can you explain in more detail what you want to do, perhaps attach a sample workbook?
Dec 22 2020 04:53 AM
@Hans Vogelaar attached is what i have as an example. In Yellow, this is what the subtotals is producing, in Green is what i would like it to show.
Dec 22 2020 06:48 AM
SolutionYou 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.
Dec 22 2020 06:48 AM
SolutionYou 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.