digest
Short Summary
Main recommendation: Use SUMIFS with END OF THE MONTH to total daily sales into monthly revenue by building a date range (start-of-month to end-of-month) as criteria, and use a simple SUMIFS on the month-end date to total monthly ad costs. The video demonstrates both approaches on an Excel table of daily sales and a table of monthly ad costs.
Key Points
- Daily sales records must be summed by month, so a date range is needed; the month-end date alone is not enough as a criteria for revenue.
- The method derives each month's beginning by taking END OF THE MONTH of the previous month and adding one day, giving a lower and upper limit.
- Monthly ad costs already carry a month-end date, so a single-criteria SUMIFS on that date is sufficient.
- Converting the data to Excel tables makes column references (for example, the Date and Sales fields) easy to insert into formulas.
Details Worth Noting
- Concrete instructions:
- Convert the daily sales range to an Excel table so fields can be referenced by name.
- In a helper cell, enter =EOMONTH(start_date, -1) to get the last day of the previous month, then add 1 to get the first day of the month. Use this for the lower limit.
- For revenue, enter =SUMIFS(Table[Sales], Table[Date], ">="&(lower_limit), Table[Date], "<="&(upper_limit)). Note that comparison operators must be in double quotes and joined with &.
- Because math runs before the join operator, (lower_limit + 1) does not need extra parentheses in the criteria string.
- For costs, enter =SUMIFS(Table[Add Costs], Table[End of Month], month_end_date), using the month-end date as the single criterion.
- Copy both formulas down the monthly rows, then use Alt+= to insert a SUM at the bottom of each column to check totals.
- Format the EOMONTH result as a date (Ctrl+1, Number, Date) since it displays as currency by default.
- EOMONTH's second argument controls the month: 1 returns next month's end, 0 the current month, and -1 the previous month.
- The video notes that Excel performs math before joining text, which is why the plus operation in the criteria needs no special parentheses.
Bottom Line
Monthly totals from daily records need a start-to-end date range in SUMIFS, while monthly records can be totaled by matching the month-end date directly.
