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)
