Sum by Month: How to Total Monthly Data

This article explains how to sum data by month, a common task when working with dates and amounts in spreadsheets or databases. You’ll learn the core formulas, step-by-step methods, and common pitfalls to avoid, so you can accurately total sales, expenses, or any other metric for each calendar month.

Formula

The most straightforward way to sum by month is to use a helper column that extracts the month (and optionally year) from each date, then use SUMIF or SUMIFS. The formulas are:

• Extract month number: =MONTH(date) returns 1–12.
• Extract year: =YEAR(date) returns the four-digit year.
• Sum by month (single year): =SUMIF(month_range, 1, amount_range) sums all amounts where the month equals 1 (January).
• Sum by month across multiple years: =SUMIFS(amount_range, month_range, 1, year_range, 2023) sums amounts for January 2023 only.

In the calculator above, you enter a date and an amount; it returns the month number and year for that date. To sum a full dataset, you would apply the SUMIF formula to a column of such extracted months.

Worked Example

Suppose you have a small sales log with dates and amounts. Using the default values from the calculator—date 2023-01-15 and amount $100—you first extract the month: =MONTH(2023-01-15) gives 1. The year is =YEAR(2023-01-15) giving 2023. Now, if you have a list of all January 2023 sales, you would use =SUMIFS(amount_range, month_range, 1, year_range, 2023). For this single entry, the sum is simply $100, matching the calculator’s output.

When to Use Sum by Month

Monthly totals are essential for:

• Tracking sales or revenue trends over time.
• Budgeting and expense analysis.
• Reporting performance to stakeholders.
• Identifying seasonal patterns or anomalies.

Any dataset with a date column and a numeric value can be summarized by month. The approach works in Excel, Google Sheets, and most database query languages (with slight syntax differences).

How to Sum by Month in Excel and Google Sheets

Here’s a step-by-step method for a typical spreadsheet:

1. Ensure your data has a date column (e.g., column A) and a value column (e.g., column B).
2. Add two helper columns: one for month number (=MONTH(A2)) and one for year (=YEAR(A2)), if you have multiple years.
3. Create a summary table with months 1–12 (or month names).
4. Use =SUMIF(month_range, 1, value_range) for a single year, or =SUMIFS(value_range, month_range, 1, year_range, 2023) for a specific year.
5. Drag the formula down for each month.

In Google Sheets, the same formulas work. For a more dynamic approach, you can use a pivot table: drag the date field to Rows, group by Month, and drag the value field to Values.

Common Mistakes and How to Avoid Them

• Ignoring the year: If your data spans multiple years, summing by month number alone will combine January 2022 and January 2023. Always include the year in your criteria.
• Text vs. date: If your date column is stored as text, MONTH() will return an error. Convert text to dates using DATEVALUE or the built-in text-to-columns tool.
• Blank or zero values: SUMIF ignores blanks, but if you have zero values they are included. That’s usually fine, but be aware if you want to count only non-zero entries.
• Locale differences: Some systems use different date formats (e.g., DD/MM/YYYY vs. MM/DD/YYYY). Always verify that your date column is recognized as a date by the software.

Alternative Methods: Pivot Tables and Database Queries

If you prefer not to use formulas, a pivot table is the fastest way to group by month:

1. Select your data range.
2. Insert a pivot table (Excel) or create one (Google Sheets).
3. Drag the date field to Rows, then right-click and select “Group� → “Months�.
4. Drag the value field to Values; it will sum by default.

For SQL databases, you can use SELECT strftime('%Y-%m', date_column) AS month, SUM(amount) FROM table GROUP BY month; in SQLite, or DATE_FORMAT(date_column, '%Y-%m') in MySQL. The concept is the same: extract a month key and group by it.

Frequently Asked Questions

How do I sum by month in Excel without a helper column?

You can use SUMPRODUCT: =SUMPRODUCT((MONTH(date_range)=1)*(YEAR(date_range)=2023)*amount_range). This works without helper columns but is less efficient for large datasets.

Can I sum by month name instead of number?

Yes, use TEXT(date, "mmmm") to get the full month name, then SUMIF with that text. However, be aware that month names are alphabetical, not chronological, in sorting.

What if my data has multiple years?

Always include the year in your criteria. Use SUMIFS with both month and year columns, or group by year and month in a pivot table.

Why is my SUMIF returning 0?

Check that your criteria match exactly. For month numbers, ensure you’re using numbers (1–12), not text. Also verify that the range sizes are identical and that dates are true dates, not text.

How do I sum by month in Google Sheets?

The same formulas work. For a dynamic summary, use QUERY: =QUERY(A:B, "select month(A), sum(B) where A is not null group by month(A)"). Note that month() in Google Sheets returns 0 for January, so add 1 if needed.

Related

• Fraction Solver with Solution Steps
• MKV, MP4 and M4V Video Converters: 10 Top Picks
• Trail Camera Data Recovery: How to Get Photos Back

in d | Date | - | 2023-01-15 | 0
in a | Amount | $ | 100 | 0
out month | Month (1-12) | - | month(d) | 0
out year | Year | - | year(d) | 0