In this tutorial, we will show the simple formulas to sum the values by month, ignore year in Excel Office 365. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
General Formula:
- To sum by month, ignoring year in Excel, use the below formula.
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Syntax Explanations:
- SUMPRODUCT – The SUMPRODUCT function multiplies the corresponding arrays or ranges and returns the sum of the products.
- MONTH – This function extracts the month number ranging from 1 (JAN) to 12 (DEC) from the given date and displays the output. Read more on the MONTH Function.
- Date – It represents the input dates that are given in the worksheet.
- Amount – It is the input values.
- Month – It is the criteria that help to sum the values.
- Comma symbol (,) – It is a separator which helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
- Multiplication (*) – In this symbol will multiply any two values or numbers.
Example:
- In the below image, we will give the input values in Column B, and Column D.
- Then, define the name for input ranges as Dates and Amount.
- After that, enter the given formula in the selected formula bar section.
- Finally, it will display the result as shown in the below image.
Conclusion:
From this tutorial, we guided you to know the simple formulas used to sum the values by month, ignore the year in Excel Office 365. Hope that you like this article. Don’t forget to share your worthwhile feedback in the below comment section. Thank you so much for Reading!! Keep learning on Geek Excel!! *and Excel Formulas *!!
Top comments (0)