Although the **AVERAGEIF** and **AVERAGEIFS** function can sum values by a given condition, they won’t work if you want to use the output in other functions. In this article, we’re going to show you how to create a year-by-year summation calculator with help of the **SUMPRODUCT** function.

## Syntax

=SUMPRODUCT( ( YEAR( date values ) = year value ) * sum values )

## Steps

- Start with the
**=SUMPRODUCT(**function - Open a parenthesis to wrap the
*criteria range – criteria*pair**(** - Enter the
*criteria range – criteria*condition by date and year numbers**YEAR($B$3:$B$12)=E3** - Close the parenthesis and add an asterisk to multiple condition and value arrays
**)*** - Select the values to add
**$C$3:$C$12** - Type in
**)**to close the SUMPRODUCT function and finish the formula

## How

The **SUMPRODUCT** function’s ability to handle arrays without array formulas provides an advantage over the limitations of **SUMIF** and **SUMIFS** functions. The **SUMPRODUCT** can evaluate and return arrays from *range – value* conditions. We use this functionality to run the **YEAR** function with array of date values and to resolve *criteria range – criteria* pairs.

YEAR($B$3:$B$12)=E3 returns *{ TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}*

The **SUMPRODUCT** function can sum the values in its argument array, if there is only one argument. However, we must first determine which values to include in the summation. We can filter those that meet our conditions by multiplying them with the Boolean array. In the results array, we get the actual values for the dates that meet the condition, and zeroes for the others.

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}* {4000;9000;6000;5000;9000;3000;9000;2000;9000;8000} returns *{ 4000;9000;6000;5000;9000;3000;9000;0;0;0}*

The last step is to add the values in the result array.

=SUMPRODUCT((YEAR($B$3:$B$12)=E3)*$C$3:$C$12)