## Explanation

The goal of this example is to sum amounts by fiscal year, when the fiscal year begins in July. The first approach is a self-contained formula based on the SUMPRODUCT function. The second method uses SUMIF with column D as a helper column. Either approach will work correctly, and the best option depends on personal preference.

### 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:

```
=YEAR(B5)+(MONTH(B5)>=7)
```

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:

```
=SUMPRODUCT(--(YEAR(date)+(MONTH(date)>=7)=F5),amount)
```

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:

```
--(YEAR(date)+(MONTH(date)>=7)=F5)
```

The main part of the formula simply returns the fiscal year for each date in the named range date:

```
YEAR(date)+(MONTH(date)>=7
```

Because there are 12 dates in this range, the result is 12 values in an array like this:

```
{2021;2021;2021;2021;2021;2021;2022;2022;2022;2022;2022;2022}
```

These are then compared to the year in F5 (2021) and the result is an array with 12 TRUE and FALSE values:

```
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
```

A double negative is used to coerce the TRUE and FALSE values to 1s and 0s, which yields:

```
{1;1;1;1;1;1;0;0;0;0;0;0}
```

This array is returned directly to the SUMPRODUCT function as *array1*:

```
=SUMPRODUCT({1;1;1;1;1;1;0;0;0;0;0;0},amount)
```

*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:

```
{2350;750;1000;1200;1850;550;0;0;0;0;0;0}
```

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:

```
=SUMIF(FY,F5,amount)
```

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.