I have a daily sales spreadsheet. I'd like to sum monthly and annual sales. At present each month I create a formula that's like:
SUM(Daily::C365:C391)
Where C is the column of values to sum and the range is the range of days for the particular month. What I'd rather do is sum the values based on a formula, like:
SUMIF(Daily::Date, month=december 2021,Daily::Gross)
How do I do the date comparison for month?
Name the table on which you are recording the daily sales Data.
Name the second table, on which the monthly totals will be calculated "Summary"
Both tables should have one Header Row, with data entry (and formulas) starting on row 2.
(the table names may be changed AFTER you have entered the formulas below, and have established that they are working correctly. Numbers will automatically revise the formulas to fit the new table names.
Data:
Record the dates in column A.
Record the amounts in column C
Data will contain one formula, entered in column E:
MONTHNAME(MONTH(A2))&" "&YEAR(A2)
Enter in E2, then fill down to the last cell in the column.
Summary:
Select cells A2:AA13.
Click the Format brush to open the Format Inspector, select Cell, then set the Data format to Text.
Close the inspector.
Enter January in cell A2, then fill down to A13 to add the rest of the month names.
Enter the year number in cell B1
Summary will have two formulas, the first entered in B2, then filled down to to B13:
SUMIF(Data::$E,$A2&" "&B$1,Data::$C)
Fill down to row 13, (and right to column C )
Row 14 is added and converted from a 'standard' row to a Footer row. To do the conversion, place the pointer between the Row reference tab of row 14 and the table. Click the v that appears and choose convert to Footer row.
After doing the conversion, click on B14, press = to open the formula editor, and enter SUM(B)
Then click the green checkmark to confirm the formula and close the editor.
With B14 still selected, fill the formula right into column C. (this will probably give you an error message until you place a year number in C1)
IN USE:
Unless you want to see it, Hide Data::E. That information is needed by the SUMIF formula, but not by the user.
You can also hide the 'future' and or 'past' columns on Summary, leaving visible only the current year and any previous years for which you want to do a comparison.
Regards,
Barry
Name the table on which you are recording the daily sales Data.
Name the second table, on which the monthly totals will be calculated "Summary"
Both tables should have one Header Row, with data entry (and formulas) starting on row 2.
(the table names may be changed AFTER you have entered the formulas below, and have established that they are working correctly. Numbers will automatically revise the formulas to fit the new table names.
Data:
Record the dates in column A.
Record the amounts in column C
Data will contain one formula, entered in column E:
MONTHNAME(MONTH(A2))&" "&YEAR(A2)
Enter in E2, then fill down to the last cell in the column.
Summary:
Select cells A2:AA13.
Click the Format brush to open the Format Inspector, select Cell, then set the Data format to Text.
Close the inspector.
Enter January in cell A2, then fill down to A13 to add the rest of the month names.
Enter the year number in cell B1
Summary will have two formulas, the first entered in B2, then filled down to to B13:
SUMIF(Data::$E,$A2&" "&B$1,Data::$C)
Fill down to row 13, (and right to column C )
Row 14 is added and converted from a 'standard' row to a Footer row. To do the conversion, place the pointer between the Row reference tab of row 14 and the table. Click the v that appears and choose convert to Footer row.
After doing the conversion, click on B14, press = to open the formula editor, and enter SUM(B)
Then click the green checkmark to confirm the formula and close the editor.
With B14 still selected, fill the formula right into column C. (this will probably give you an error message until you place a year number in C1)
IN USE:
Unless you want to see it, Hide Data::E. That information is needed by the SUMIF formula, but not by the user.
You can also hide the 'future' and or 'past' columns on Summary, leaving visible only the current year and any previous years for which you want to do a comparison.
Regards,
Barry
Hi ozRob,
In the table "Daily" I used a subset of dates to make a smaller screenshot.
Formula in cell C2: MONTHNAME(MONTH(A2))
Formula in cell D2: YEAR(A2)
In the table "Monthly", formula in cell C2: SUMIFS(Daily::B,Daily::C,A2,Daily::D,B2)
Both tables have a Header Row.
Feel free to reply with questions.
Regards,
Ian.
Hi again ozRob,
Please reply with a screen shot of a small part of your screen (shift command 4). Full screen shots (shift command 3) are often difficult to read.
First, go to Numbers > Preferences > General:
Untick Cell References: Use header names as labels. That makes it easier for other users in this forum to understand your formulas.
To take a screenshot:
1. Remove or hide any personal details before taking the screen shot.
2. Hold down the shift and command keys, then type 4. The cursor will change to crosshairs.
3. Drag over the relevant part of your screen with the column letters and row numbers showing and then release the mouse/trackpad. You will hear a "camera shutter" sound. A screen shot will appear on your desktop. It will be named Screen Shot with a date & time.
4. In a reply to a message, click on the "Mountains" icon in the Toolbar below your reply:
5. Navigate to your Desktop, click on your screen shot file, then click on Choose.
After a short pause, your screen shot will appear in your reply. Click on the image to see "drag handles" to resize the image.
Click on the Post button to send your reply.
We look forward to seeing what you see.
Regards,
Ian.
Hi Ian,
The following shows how I've done the collation of weekly values, monthly is similar but needs a bigger snapshot. Here's a section of the daily sales spreadsheet showing the week ending on Sat 24 July:
The formula in B679 (y-m column) is:
YEAR($A679)&"-"&MONTH($A679)
The resulting value (e.g. 2021-7 for July) is used to collate monthly data. The formula in column C (Weeknum) is:
YEAR($A679)&"W"&WEEKNUM($A679,2)
which gives a value like 2021W30 for the week starting 19 July and is used for a weekly summary. I've used ISO 8601 week numbering and formatting for convenience.
This is the Weekly spreadsheet where I use the week number to summarise daily data:
Column B (Weeknum) has the same formula as Daily::C to generate week numbers:
YEAR($A95)&"W"&WEEKNUM($A95,2)
In column D (Pieces) uses the week numbers to sum the pieces for the week:
SUMIF(Daily::C,"="&B95,Daily::D)
Which sums the values in column D where the values in Daily::C equal the value in Weekly::B, which in the example is the total pieces for the week ending 24 July (303).
Normally I have these reference columns hidden as they're only useful for formulas.
Hopefully that explains what I've done.
Rather than struggle with entering and debugging formulas why not take advantage of powerful built-in features in Numbers. Just use Pivot Tables? You'll have your answer in a few seconds.
Simply click in the table with the data and from the menu choose Organize > Create Pivot Table > On Current Sheet. Then drag the respective fields down into the Rows and Values boxes.
You can switch from month-year to just year by simply clicking the circled ⓘ and choosing from the dropdown:
If you change or add data then you can update a Pivot Table by clicking in it and choosing Organize > Refresh Pivot Table from the menu.
SG
Thanks, I figured out I had to do something similar so created a column named "y-m" and formatted as YYYY-MM using:
YEAR($A2)&"-"&MONTH($A2)
so it can compared as text, then the formula to do the sum is:
SUMIF(Daily::$'y-m',"="&$'y-m' Feb 20,Daily::Total)
the "Feb 20" comes from the header column of the row.
I enter the formula in one cell and can drag the formula down the column and get the totals.
It's a bit annoying to have to create columns with data just to do formulas, I should be able to put it all in one formula like:
"SUMIF(Daily::Date, YEAR(Date) = 2021 & MONTH(Date) = 12, Daily:Total)"
but Numbers doesn't seem to understand that it has to pass the cell value to the YEAR and MONTH functions, they want a discrete cell reference.
BTW, how I get the function browser to say visible? It only appears when I'm editing a function in a cell, which is annoying. I'd rather be able to make it visible always.
Date in SUMIF comparison
