Excel Formulas to Calculate the Sum by Fiscal Year ~ Quick Method!!

Assume that you have a list of dates and amounts, here you need to calculate the sum of the amounts based on the fiscal year in Excel , What can you do? Today I introduce you to some formulas to solve this problem. Let’s jump into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Generic Formula:

If you want to calculate the sum based on the year in Excel, you can use the below formula.

*=SUMPRODUCT(–(YEAR(date)+(MONTH(date)>=7)=F5),amount) *

Syntax Explanations:

SUMPRODUCT – The SUMPRODUCT Function in Excel multiplies the corresponding arrays or ranges and returns the sum of the products.

YEAR – This function helps to extract the year from the given date and returns the year as a 4-digit number. Read more on the YEAR function.

MONTH – In Excel, the MONTH function returns the month of a date.

*Date * – It specifies the input dates from your worksheet.

Comma symbol (,) – It is a separator that helps to separate a list of values.

Plus operator (+) – This symbol is used to add the values.

Minus Operator (-) – This symbol will help to subtract any two values.

Parenthesis () – The main purpose of this symbol is to group the elements.

Example:

Let’s consider the below example image.

First, we will enter the input dates in Column B , Column C, and Column D.

Here we are going to calculate the sum values based on the fiscal year.

Select any cell and type the above-given formula.

Finally, press ENTER to get the result out, if you need, drag the fill handle over range to apply the formula.

Bottom-Line:

Hope you like this article on how to calculate the sum by fiscal year in Excel. Please write us below if something is unclear or if you need further assistance on this subject.

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **

Read Also:

Excel Formulas to Parse Time String to Time ~ A Complete Tutorial!!

How to Count missing values in a list in Excel Office 365?

Excel Formulas to Find the Next Working Day ~ Quick Solution!!

How to Find the Last Column of Data in Excel Office 365?