How to Sum Values Based on Month in Excel (Formula & Examples)

Learn how to sum values based on month in Excel using SUMIFS, SUMPRODUCT, and EDATE formulas with practical examples for monthly reports.

 

How to Sum Values Based on Month in Excel (Formula & Examples)

   Learn how to sum values based on a month in Excel using simple formulas. Discover how to use SUMIFS, SUMPRODUCT, and EDATE to calculate monthly sales, expenses, and transaction totals with practical examples.

How to Sum Values Based on Month in Excel (Formula & Examples)

Working with monthly sales, expenses, employee payments, or business transactions in Excel often means calculating totals for a particular month. For example, you might want to find your total sales in January, calculate expenses for March, or compare monthly revenue throughout the year.

Instead of manually filtering dates and adding numbers, you can use Excel formulas to calculate monthly totals automatically.

Excel offers several ways to do this. The SUMIFS function is one of the most reliable options because it allows you to sum values within a specific date range. You can also use SUMPRODUCT or combine SUMIFS with a month selection dropdown to create a more flexible monthly report.

In this guide, you'll learn how to sum values based on a month in Excel, with practical examples and formulas you can use in your own spreadsheets.

Understanding the Data for Monthly Calculations

Before writing a formula, let's look at a simple sales dataset.

Imagine you maintain a sales report with transaction dates and corresponding amounts.

DateProductSales Amount
05-Jan-2026Laptop25,000
12-Jan-2026Monitor12,000
20-Feb-2026Keyboard3,000
08-Feb-2026Mouse1,500
15-Mar-2026Laptop30,000
22-Mar-2026Monitor15,000
28-Jan-2026Keyboard2,500

For this example:

  • Column A contains transaction dates.

  • Column B contains product names.

  • Column C contains sales amounts.

Suppose you want to calculate the total sales for January, February, and March separately.

You can do this with a simple SUMIFS formula.

Using SUMIFS to Sum Values Based on a Month

The SUMIFS function is particularly useful when you want to add numbers that meet multiple conditions. In this case, the conditions are the beginning and ending dates of a particular month.

The general syntax is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

For monthly calculations, the formula checks whether each transaction date falls between the first and last day of the selected month.

Assuming your dates are in column A and sales amounts are in column C, use this formula to calculate January sales:

=SUMIFS(C2:C8,A2:A8,">="&DATE(2026,1,1),A2:A8,"<"&DATE(2026,2,1))

How this formula works:

  • C2:C8 tells Excel which values to add.

  • A2:A8 contains the transaction dates.

  • ">="&DATE(2026,1,1) includes transactions from January 1 onward.

  • "<"&DATE(2026,2,1) excludes transactions starting from February 1.

Using this formula, the January sales total is ₹39,500.

The advantage of using the first day of the following month as the upper limit is that it also handles dates containing times correctly.

Using SUMIFS with a Cell Reference for Any Month

Typing a new date into the formula every time can become inconvenient, especially when preparing reports for several months.

A better approach is to enter the month you want to calculate in a separate cell and let Excel update the total automatically.

For example, enter a date such as 01-Jan-2026 in cell E2.

Then use:

=SUMIFS(C2:C8,A2:A8,">="&E2,A2:A8,"<"&EDATE(E2,1))

How it works:

  • E2 contains the first date of the selected month.

  • EDATE(E2,1) returns the first date of the next month.

  • SUMIFS adds all sales between these two dates.

If you change E2 to 01-Feb-2026, Excel automatically calculates February sales.

You can format E2 as MMMM YYYY to display the month as January 2026 instead of showing the full date.

This method is useful for monthly financial reports, sales dashboards, expense trackers, and business performance summaries.

Using SUMPRODUCT to Sum Values by Month

Another way to calculate monthly totals is with the SUMPRODUCT function.

This method is useful when you want to match the month and year of each transaction date without creating separate start and end dates.

Use this formula to calculate January 2026 sales:

=SUMPRODUCT((MONTH(A2:A8)=1)*(YEAR(A2:A8)=2026)*C2:C8)

Formula explanation:

  • MONTH(A2:A8)=1 identifies dates in January.

  • YEAR(A2:A8)=2026 checks that the dates belong to 2026.

  • C2:C8 contains the sales values to add.

  • SUMPRODUCT multiplies the matching conditions and adds the qualifying amounts.

The result is ₹39,500.

One important detail: This formula checks both the month and year. Without the year condition, Excel could combine January sales from multiple years.

For larger datasets, SUMIFS with date boundaries is generally a simpler and more efficient choice.

Summing Monthly Values Using the Month Name

Sometimes, your spreadsheet contains dates, but your monthly report displays month names such as January, February, and March.

You can still calculate the totals using SUMIFS.

Suppose cell E2 contains the month name January.

Use:

=SUMIFS(C2:C8,A2:A8,">="&DATEVALUE("1 "&E2&" 2026"),A2:A8,"<"&EDATE(DATEVALUE("1 "&E2&" 2026"),1))

This formula converts the month name into a valid date, identifies the beginning of that month, and calculates the total sales until the next month begins.

For example:

MonthTotal Sales
January₹39,500
February₹4,500
March₹45,000

This approach is helpful when creating monthly reports where users prefer selecting or viewing month names instead of entering complete dates.

Note: The formula assumes that E2 contains a valid English month name and that the year is 2026.

Creating an Automatic Monthly Summary in Excel

If you regularly prepare monthly reports, you can create a summary table that calculates all monthly totals automatically.

For example, enter the first day of each month in column E and use column F for the monthly totals.

MonthTotal Sales
Jan-2026₹39,500
Feb-2026₹4,500
Mar-2026₹45,000
Apr-2026₹0
May-2026₹0

Enter 01-Jan-2026 in E2, 01-Feb-2026 in E3, and continue for the remaining months.

In F2, enter:

=SUMIFS($C$2:$C$1000,$A$2:$A$1000,">="&E2,$A$2:$A$1000,"<"&EDATE(E2,1))

Drag the formula down to calculate the totals for every month.

The dollar signs keep the source data ranges fixed while the month reference changes automatically.

This setup is especially useful for:

  • Monthly business sales reports.

  • Household expense tracking.

  • Employee salary summaries.

  • Inventory purchase records.

  • Monthly profit and loss statements.

Common Mistakes to Avoid When Summing Values by Month

Even a correct formula can return unexpected results if the source data is not properly formatted.

Here are some common problems to check.

Dates Stored as Text

Excel may not recognise dates entered as text. If your formula returns zero even though transactions exist, check whether the dates are genuine Excel dates.

You can test a date using:

=ISNUMBER(A2)

A result of TRUE generally indicates that Excel recognises the value as a number, which is how Excel stores dates.

Forgetting the Year Condition

A formula that checks only the month number can combine January transactions from different years.

Always include the year when using MONTH and SUMPRODUCT, or use complete date boundaries with SUMIFS.

Using Incorrect Date Ranges

Make sure the start date is the first day of the selected month and the end condition is the first day of the following month.

This avoids accidentally excluding transactions from the last day of a month.

Numbers Stored as Text

If your sales amounts are stored as text, Excel may not include them correctly in calculations. Convert them into numeric values before using the formula.

Incorrect Cell References

Check that your sum range and date range cover the same number of rows. For example, C2:C100 should correspond to A2:A100.

Which Excel Formula Should You Use?

Different formulas are useful for different monthly reporting situations.

MethodBest ForFormula
SUMIFS with DATECalculating a fixed month's total=SUMIFS(C2:C8,A2:A8,">="&DATE(2026,1,1),A2:A8,"<"&DATE(2026,2,1))
SUMIFS with EDATEDynamic monthly calculations=SUMIFS(C2:C8,A2:A8,">="&E2,A2:A8,"<"&EDATE(E2,1))
SUMPRODUCTMatching month and year=SUMPRODUCT((MONTH(A2:A8)=1)*(YEAR(A2:A8)=2026)*C2:C8)
SUMIFS with Month NameReports using month names=SUMIFS(C2:C8,A2:A8,">="&DATEVALUE("1 "&E2&" 2026"),A2:A8,"<"&EDATE(DATEVALUE("1 "&E2&" 2026"),1))

For most everyday Excel tasks, SUMIFS with EDATE is the recommended option. It is easy to understand, works well with dynamic reports, and handles transactions containing both dates and times.

Final Thoughts

Summing values based on a month in Excel becomes much easier once you understand how to work with date ranges.

The SUMIFS function is usually the best choice because it allows you to calculate monthly sales, expenses, purchases, or other values without manually filtering your data.

If you want a flexible monthly report, use SUMIFS with EDATE. Simply change the month in your selected cell, and Excel will update the total automatically.

With a little practice, you can use these formulas to build accurate monthly summaries and make your Excel reports much easier to manage.

Frequently Asked Questions

Can I sum values for a specific month in Excel?

Yes. Use SUMIFS with the first day of the month and the first day of the following month as date criteria.

Which Excel function is best for monthly totals?

SUMIFS is generally the best choice because it handles date ranges and multiple conditions easily.

Can I calculate monthly totals for different years?

Yes. Include the year in your date criteria to ensure Excel calculates values for the correct year.

Can Excel automatically calculate totals when I change the month?

Yes. Use SUMIFS with a cell reference and EDATE. Changing the selected month updates the result automatically.

Can I sum monthly sales from multiple years separately?

Yes. Create separate monthly date ranges for each year or use SUMPRODUCT with both MONTH and YEAR conditions.

COMMENTS

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content