Feb 22 2022 12:16 PM
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
Feb 22 2022 12:42 PM
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.
Feb 23 2022 06:05 AM
Feb 23 2022 06:08 AM
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.
Feb 23 2022 07:12 AM
SolutionHello Hans.
I understand that. I believe I had converted it; but I will double check.
Thanks,
FastEddyG2
Feb 23 2022 10:28 AM
Hi Hans.
You got it! I had not properly converted Excel page to Table. It works now!
Thanks for your guidance.
Best regards,
FastEddyG2
Feb 28 2022 03:21 AM
Feb 23 2022 07:12 AM
SolutionHello Hans.
I understand that. I believe I had converted it; but I will double check.
Thanks,
FastEddyG2