Subtotals in a table

[FastEddyG2]

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

[#2]

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

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:

Click OK.

[#3]

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

[#4]

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

[#5 · Accepted solution]

@Hans Vogelaar 

Hello Hans.

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

Thanks,

FastEddyG2

[#6]

@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

[#7]

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!