You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Hello numbers friends, been a while...
So...
I have a sheet with item transactions, showing sales on different days. I’m trying to create a summary sheet to display the monthly and quarterly totals from the main sales sheet. However, I’m struggling to get the right formulas in the summary section. I prefer not to use pivot tables and would rather use straightforward formulas to achieve the results. screenshoots is included here.
Thanks!
MacBook Air, macOS 14.5
If you want to avoid using pivot tables, you have a few options. The path I'd take would be to use hidden calculated fields to summarize your data.
For example, on your Sales table, insert a 'Month' column that calculates the "MM/YY" value for any given date using the formula:
=DATE(YEAR($A2),MONTH($A2),1)
this will calculate the first day of the month specified in column A.
I chose to format this cell to show 'January 2024' (e.g. omitting the day number, but that's not critical)
You can hide this column so you don't have to see it (or accidentally edit it).
Then on your Summary sheet, you use SUMIF() statements to compare the date in Summary::Monthly::A to the (hidden) column on the Sales table:
=SUMIF(Sales::Sales Data::Month, $A2, Sales::Sales Data::Total Sales)
This tells numbers to look in the (hidden) 'Month' column on the Sales Data sheet and check the values against $A2 on the Summary sheet. For every match it adds the corresponding value from the Total Sales column.
You can use this to summarize any of the data from the other columns, too.
Repeat the exercise for the Quarterly Table, using a (hidden) column on either the Sales Data or the Monthly table that calculates the corresponding Quarter.
If you want to avoid using pivot tables, you have a few options. The path I'd take would be to use hidden calculated fields to summarize your data.
For example, on your Sales table, insert a 'Month' column that calculates the "MM/YY" value for any given date using the formula:
=DATE(YEAR($A2),MONTH($A2),1)
this will calculate the first day of the month specified in column A.
I chose to format this cell to show 'January 2024' (e.g. omitting the day number, but that's not critical)
You can hide this column so you don't have to see it (or accidentally edit it).
Then on your Summary sheet, you use SUMIF() statements to compare the date in Summary::Monthly::A to the (hidden) column on the Sales table:
=SUMIF(Sales::Sales Data::Month, $A2, Sales::Sales Data::Total Sales)
This tells numbers to look in the (hidden) 'Month' column on the Sales Data sheet and check the values against $A2 on the Summary sheet. For every match it adds the corresponding value from the Total Sales column.
You can use this to summarize any of the data from the other columns, too.
Repeat the exercise for the Quarterly Table, using a (hidden) column on either the Sales Data or the Monthly table that calculates the corresponding Quarter.
Stvylife wrote:
P.S. Regarding pivot tables, I’m not entirely sure, but I think they require some kind of refresh after making changes, which was a drawback for me. Live formulas are much better.
Pivot Tables in Numbers (and other spreadsheet apps) do require a refresh. That is easily done, just a click of a button or a menu pick, literally takes a second.
Refresh a pivot table in Numbers on Mac - Apple Support
For aggregation by month and quarters, I think you'll find Pivot Tables a big time saver.
SG
Thanks for the clarification! It got me close enough to figure it out. I created another hidden column for the quarters, and it made sense once I implemented the hidden columns, especially when resetting the date to the 1st. Next, I extracted the Q1…Q4 from the dates.
P.S. Regarding pivot tables, I’m not entirely sure, but I think they require some kind of refresh after making changes, which was a drawback for me. Live formulas are much better.
show monthly and quarterly totals
