How to Calculate Weekly and Monthly Totals on Specific Dates in Excel
Question details
The user needs to calculate daily costs and conditionally display weekly and monthly totals only on specific dates (e.g., Saturdays and the last day of the month) rather than on every row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a financial, payroll, or time-tracking spreadsheet where periodic summaries are needed exclusively on the closing dates of the periods.
- Observed behavior
- The user wants totals to appear conditionally on chosen days and needs to resolve the 'formula omits adjacent cells' error that prevents easy formula copying down the column.
Ensure your date column is formatted properly as dates in Excel so the WEEKDAY and EOMONTH functions can evaluate them correctly.
Use Conditional SUMIFS Formulas for Specific Dates
Use a combination of IF, WEEKDAY, EOMONTH, and SUMIFS functions to calculate totals and display them only on the desired dates.
By nesting a SUMIFS function inside an IF function, you can instruct Excel to only perform and display the calculation when the row's date matches a specific criteria, leaving the cell blank otherwise.
In cell F2, enter your daily calculation, such as =C2*E2+D2, and press Enter.
To display weekly totals only on Saturdays, enter =IF(WEEKDAY(A2)=7,SUMIFS(F$2:F2,A$2:A2,">"&A2-7,A$2:A2,"<="&A2),"") in cell G2.
To display monthly totals only on the last day of the month, enter =IF(A2=EOMONTH(A2,0),SUMIFS(F$2:F2,A$2:A2,">"&EOMONTH(A2,-1),A$2:A2,"<="&A2),"") in cell H2.
Select cells F2, G2, and H2, and drag the fill handle (the small square at the bottom-right corner of the selection) down to apply the formulas to the rest of the column.
Create a PivotTable for Monthly Summaries
Use a PivotTable if you want an automated, summarized view of your data without building complex conditional formulas.
Easily Manage Financial Data with WPS Spreadsheet
You can effortlessly build conditional formulas like SUMIFS, WEEKDAY, and EOMONTH using WPS Spreadsheet. It provides seamless compatibility with Excel formulas and offers an intuitive interface for managing your daily, weekly, and monthly totals.
- 1. Open your data file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet.
- 2. Apply conditional formulas: Enter the IF and SUMIFS formulas into your weekly and monthly total columns just as you would in Excel.
- 3. Customize error checking rules: Go to Menu > Options > Error Checking to customize your formula rules and easily prevent the 'omits adjacent cells' warning globally.
Frequently Asked Questions
Why does the weekly total formula use WEEKDAY(A2)=7?
The WEEKDAY function returns a number from 1 to 7 representing the day of the week. By default, 1 is Sunday and 7 is Saturday. Checking if it equals 7 ensures the total only displays on Saturdays.
What does the EOMONTH function do in the monthly total formula?
EOMONTH calculates the last day of the month for a given date. EOMONTH(A2,0) returns the last day of the current month, and EOMONTH(A2,-1) returns the last day of the previous month. This allows the SUMIFS formula to isolate the exact date range for the current month.
How do I make the weekly total appear on a Sunday instead of a Saturday?
Change the condition in the formula from WEEKDAY(A2)=7 to WEEKDAY(A2)=1. Sunday is represented by the number 1 in the default WEEKDAY function settings.
