where date (B5:B16), amount (C5:C16)), and FY (D5:D16)) are named ranges. When copied down, this formula returns sum of amounts for the fiscal years in F5 and F6 as shown, based on a fiscal start in July.
Helper column
To make the example easier to understand and to provide a simple way to use the SUMIF function (see below), column D is set up as a helper column that displays a fiscal year for each row, based on a July start. The formula in cell D5 is: This formula is explained in detail here.
SUMPRODUCT option
One approach is to use the SUMPRODUCT function together with the YEAR and MONTH functions as seen in the example, where the formula in G5 is: Here, SUMPRODUCT is configured with two arrays. The first array (array1) is set up to filter values based on the fiscal year in column F: The main part of the formula simply returns the fiscal year for each date in the named range date: Because there are 12 dates in this range, the result is 12 values in an array like this: These are then compared to the year in F5 (2021) and the result is an array with 12 TRUE and FALSE values: A double negative is used to coerce the TRUE and FALSE values to 1s and 0s, which yields: This array is returned directly to the SUMPRODUCT function as array1: Array2 is the named range amount (C5:C16) which contains values to sum. SUMPRODUCT multiplies the corresponding items in each array which results in an array like this: Notice amounts in fiscal year 2022 have been “zeroed out”. Finally, SUMPRODUCT returns the sum of all items in the array, 7700.
SUMIF option
Because each row in the data already contains a calculated value for fiscal year in column D, we can use this column directly in the SUMIF function as a criteria range. With SUMIF, the formula in G5, copied down, is: This is a much simpler formula than the SUMPRODUCT formula above, but it depends on the fiscal year values being part of the data. In contrast, the SUMPRODUCT option is self-contained. We can’t build a similar self-contained formula with SUMIF because of innate limitations of the function. Namely, the cells being evaluated must be a range, they can’t be an array generated by another formula.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
