SOLVED

Subtotals after each group

New Contributor

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?GcZ0T.jpg

6 Replies

@YasirAman 

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.

@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?

@YasirAman 

Can you explain in more detail what you want to do, perhaps attach a sample workbook?

@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. 

best response confirmed by YasirAman (New Contributor)
Solution

@YasirAman 

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.

Thanks!