Tanzim
There may be times when you need to know easily how to use SUMIF by Month and Year in Excel. For example, you may need to use an Excel formula or VBA for this purpose. Today, we’ll show you how to do SUMIF by Month and Year in Excel with various methods and lots of examples.
📁 Download Excel File
To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.
Learn to Use SUMIF by Month and Year Using These 7 Easy Ways
In the next table, we have some sales records based on certain dates. To show you how to sum up sales we can base on a specific month of the year, I’ll utilize this example. I did this example with Excel 365. But all other versions work just as fine.
1. Utilising SUMIFS Function
We will use the SUMIFS function here. The SUMIFS function adds numbers together. You can add single values, references to cells or ranges, or a combination of all three on criteria. We will use the DATEDIF function. It determines how many days, months, or years there are between two dates. Start date, end date, and format are the three parameters required by the DATEDIF function.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select cell G6. Then we put the formula below:
- Then we press The final result will be like this.
🔨 Formula Breakdown
👉 D6:D15 means the sales range, C6:C15 indicates the range containing criteria.
👉 “>=”&DATE(2022,1,1) portion is used as first criteria and DATE gives back the month’s first date.
👉 “<=”&DATE(2022,1,31) portion is used as second criteria and DATE gives back the month’s last date.
2. Inserting SUMIFS Function with a Certain Range of Period
We will use the SUMIFS function here also. The SUMIFS function adds numbers together. You can add single values, references to cells or ranges, or a combination of all three on criteria. We will use the DATEDIF function. It determines how many days, months, or years there are between two dates. Start date, end date, and format are the three parameters required by the DATEDIF function.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select cell G6. Then we put the formula below:
- Then we press The final result will be like this.
🔨 Formula Breakdown
👉 D6:D15 means the sales range, C6:C15 indicates the range containing criteria.
👉 “>=”&DATE(2022,1,1) portion is used as first criteria and DATE gives back the month’s first date.
👉 “<=”&DATE(2022,1,31) portion is used as second criteria and DATE gives back the month’s last date.
3. Combining SUMIFS and EOMONTH Functions
We will use the SUMIFS function here also. The SUMIFS function adds numbers together. You can add single values, references to cells or ranges, or a combination of all three on criteria. The EOMONTH function gives back the serial number for the last day of the month that is the given number of months before or after the start date.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select cell G6. Then we put the formula below.
- Then we press Enter and drag down the formula.
- The final result will be like this.
🔨 Formula Breakdown
👉 $D$6:$D$15 indicates the sales range and $C$6:$C$15 means the criteria.
👉 “>=”&F6 indicates first criteria and F6 is the month’s first date.
👉 “<=”&EOMONTH(F5,0) indicates second criteria and EOMONTH gives back the month’s last date.
4. Implementing SUMPRODUCT Function
To count the matches alongside two columns, we can only use SUMPRODUCT function. SUMPRODUCT counts and adds up all the ones and zeros in an array.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select cell G6. Then we put the formula below.
- Then we press Enter and the final result will be like this.
🔨 Formula Breakdown
👉 $D$6:$D$15 indicates the sales range and $C$6:$C$15 means the criteria.
👉 MONTH(C6:C15) will give you dates in the month, and if it’s 2, that means it’s February.
👉 YEAR(C6:C15) will give you the years within the dates.
5. Adding Up Based on Certain Criteria
We will use the SUMIFS function here also for inserting SUMIF by month and year. The SUMIFS function adds numbers together. You can add single values, references to cells or ranges, or a combination of all three on criteria. We will use the DATEDIF function. It determines how many days, months, or years there are between two dates. Start date, end date, and format are the three parameters required by the DATEDIF function.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select cell G6. Then we put the formula below.
- Then we press The final result will be like this.
🔨 Formula Breakdown
👉 D6:D15 means the sales range, New is the criteria here.
👉 “>=”&DATE(2022,1,1) portion is used as first criteria and DATE gives back the month’s first date.
👉 “<=”&DATE(2022,1,31) portion is used as second criteria and DATE gives back the month’s last date.
6. Using IF and SUM Functions with a Criteria
Now we can do the same work by using SUM function and IF function for inserting SUMIF by month and year. We will use SUM here also. SUM function adds numbers together. You can add single values, references to cells or ranges, or a combination of all three. IF function enables you to compare a value to what you anticipate logically.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select cell G6. Then we put the formula below.
- Then we press The final result will be like this.
🔨 Formula Breakdown
👉 D6:D15 means the sales range, New is the criteria here.
👉 MONTH(C6:C15) will give you the dates in the month, and if it’s 2, that means it’s February.
👉 YEAR(C6:C15) will give you the year within the dates.
7. Utilizing a Pivot Table
Pivot Table is a powerful tool for calculating, summarizing, and analyzing data that lets you see comparisons, various patterns, and some trends in the data. Now I’ll demonstrate how to count the number of occurrences that occur each day by using Pivot Table.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select any cell, then go to Insert next to the Home tab and select Pivot Table. Then we select Table/Range from the drop-down box.
- From the new dialogue box, first, we select the whole range. Then we select Existing Worksheet, then we select cell E6 as the location of the pivot table. Finally, we press OK.
- On the right side of our sheet, a box called “Pivot Table Field” will appear.
- Here we select Date, Sales, and Years. We put them in the following areas.
- Here is the final result.
📄 Important Notes
🖊️ In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.
🖊️ A practice workbook is given so that you can practice yourself.
🖊️ All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.
🖊️ At the side of each Excel worksheet file, there is space where they can practice.
📝 Takeaways from This Article
📌 The reader will be able to know easily how to use SUMIF by Month and Year in Excel in various methods.
📌 They can utilize SUMIFS Function within a certain period.
📌 Again they can combine SUMIFS and EOMONTH Functions.
📌 Readers can also implement SUMPRODUCT Function.
📌 The readers can sum up based on certain criteria.
📌 Readers can use again use SUM and IF Functions with criteria.
📌 Finally, they can use Pivot Table.
Conclusion
That concludes today’s session. These are the methods for using SUMIF by Month and Year in Excel in various methods. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen.com, the best Excel solutions provider.
