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 average calculator with help of the SUMPRODUCT function.
Syntax
=SUMPRODUCT( ( YEAR( date values ) = year value ) * sum values ) /
SUMPRODUCT( N( YEAR( date values ) = year value ) )
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
- Close the SUMPRODUCT function )
- Add a division operator /
- Continue with SUMPRODUCT to count of dates SUMPRODUCT(
- Use the N function or double minus (--) signs to convert Boolean values into ones and zeroes N(
- Enter criteria range – criteria condition by dates and year numbers YEAR($B$3:$B$12)=D3
- Close both N and the SUMPRODUCT functions ))
How
The SUMPRODUCT function’s ability to handle arrays without array formulas provides an advantage over the limitations of AVERAGEIF and AVERAGEIFS functions. The SUMPRODUCT can evaluate and return arrays from range – value conditions. We use this functionality to run the YEAR function with an array of date values, and to resolve criteria range – criteria pairs. For example,
YEAR($B$3:$B$12)=E3 returns {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}
Since we are using a formula to calculate average values, we can divide the sum by the count of cells that meet the conditions.
Sum: =SUMPRODUCT((YEAR($B$3:$B$12)=E3)*$C$3:$C$12)
Count: =SUMPRODUCT(N(YEAR($B$3:$B$12)=E3))
Average: =SUMPRODUCT((YEAR($B$3:$B$12)=E3)*$C$3:$C$12)/SUMPRODUCT(N(YEAR($B$3:$B$12)=E3))
For more information about the sum and count please see:
