SOLVED

Subtotals in a table

%3CLINGO-SUB%20id%3D%22lingo-sub-3200825%22%20slang%3D%22en-US%22%3ESubtotals%20in%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3200825%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20friends.%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20with%20one%20page%20for%20each%20account%3B%20like%20groceries%2C%20rent%20etc.%3C%2FP%3E%3CP%3EThe%20data%20is%20already%20sorted%20on%20transaction%20date%3B%20like%201%2F14%2F2021%3C%2FP%3E%3CP%3EI%20want%20to%20present%20the%20data%20with%20a%20break%20with%20subtotal%20on%20amount%20when%20the%20month%20changes.%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20make%20this%20work%20by%20following%20the%20help%20in%20Excel%3B%20but%20have%20not%20been%20able%20to%20make%20it%20work.%3C%2FP%3E%3CP%3EI%20would%20appreciate%20it%20if%20someone%20could%20%22draw%22%20me%20a%20picture%20of%20how%20to%20make%20this%20happen%3B%20sometimes%20I%20just%20can't%20see%20the%20%22forest%20for%20the%20trees%22%20.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EFastEddyG2%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3200825%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3200894%22%20slang%3D%22en-US%22%3ERe%3A%20Subtotals%20in%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3200894%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1314969%22%20target%3D%22_blank%22%3E%40FastEddyG2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdd%20a%20column%20that%20returns%20the%20month.%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20let's%20say%20the%20transaction%20dates%20are%20in%20B2%20and%20down.%20Insert%20a%20blank%20column%20in%20column%20C.%3C%2FP%3E%0A%3CP%3EEnter%20Month%20in%20C1.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%20%3DB2-DAY(B2)%2B1%20in%20C2%20and%20fill%20down.%3C%2FP%3E%0A%3CP%3EIf%20you%20wish%20you%20can%20format%20C2%20and%20down%20as%20mmm%20yyyy%20so%20that%20it%20displays%20only%20the%20month%20and%20year%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1172.png%22%20style%3D%22width%3A%20422px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350472i0A0D5E566DDBD7CB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1172.png%22%20alt%3D%22S1172.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESelect%20any%20cell%20in%20the%20data.%3C%2FP%3E%0A%3CP%3EClick%20Subtotal%20in%20the%20Outline%20group%20of%20the%20Data%20group%20of%20the%20ribbon.%3C%2FP%3E%0A%3CP%3ESelect%20Month%20from%20the%20'At%20each%20change%20in'%20drop-down.%3C%2FP%3E%0A%3CP%3EUnder%20'Add%20subtotals%20to'%20tick%20the%20check%20box%20for%20the%20amount%20column%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1173.png%22%20style%3D%22width%3A%20321px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350473i50073FBB6D22CA25%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1173.png%22%20alt%3D%22S1173.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EClick%20OK.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3204112%22%20slang%3D%22en-US%22%3ERe%3A%20Subtotals%20in%20a%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3204112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1314969%22%20target%3D%22_blank%22%3E%40FastEddyG2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20subject%20of%20your%20question%20says%20%22Subtotals%20in%20a%20table%22.%3C%2FP%3E%0A%3CP%3EIf%20your%20data%20are%20in%20a%20table%20(created%20by%20selecting%20Insert%20%26gt%3B%20Table)%2C%20you%20cannot%20insert%20subtotals.%20Tables%20don't%20allow%20that.%20So%20you'd%20have%20to%20convert%20the%20table%20to%20a%20normal%20range.%20You%20should%20then%20be%20able%20to%20create%20subtotals.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello friends.

I have a workbook with one page for each account; like groceries, rent etc.

The data is already sorted on transaction date; like 1/14/2021

I want to present the data with a break with subtotal on amount when the month changes.

I have tried to make this work by following the help in Excel; but have not been able to make it work.

I would appreciate it if someone could "draw" me a picture of how to make this happen; sometimes I just can't see the "forest for the trees" .

Thanks

FastEddyG2

6 Replies

@FastEddyG2 

Add a column that returns the month.

For example, let's say the transaction dates are in B2 and down. Insert a blank column in column C.

Enter Month in C1.

Enter the formula =B2-DAY(B2)+1 in C2 and fill down.

If you wish you can format C2 and down as mmm yyyy so that it displays only the month and year:

S1172.png

Select any cell in the data.

Click Subtotal in the Outline group of the Data group of the ribbon.

Select Month from the 'At each change in' drop-down.

Under 'Add subtotals to' tick the check box for the amount column:

S1173.png

Click OK.

Hello Hans.
Thanks for the help. I don't know what I am doing wrong. I followed your very clear "picture" and got a bit further. However, after I "..select any cell.." the subtotal button under outline is grayed out.
Any idea why?
Thanks
FastEddyG2

@FastEddyG2 

The subject of your question says "Subtotals in a table".

If your data are in a table (created by selecting Insert > Table), you cannot insert subtotals. Tables don't allow that. So you'd have to convert the table to a normal range. You should then be able to create subtotals.

best response confirmed by FastEddyG2 (Occasional Contributor)
Solution

@Hans Vogelaar 

Hello Hans.

I understand that. I believe I had converted it; but I will double check.

 

Thanks,

FastEddyG2

 

@FastEddyG2 

Hi Hans.

You got it! I had not properly converted Excel page to Table. It works now!

Thanks for your guidance.

 

Best regards,

FastEddyG2

 

Hello Hans.
Thank you for your help. I checked and I did not properly convert it. I corrected that and now it works as it should.
Thanks again!