Although the COUNTIF and COUNTIFS functions can count 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 count cells in Excel by year.
Syntax
=SUMPRODUCT( N( YEAR( date values ) = year value ) )
Steps
- Start with the =SUMPRODUCT( function
- Use the N function, or double minus (--) signs to convert Boolean values into ones and zeroes N(
- Enter the criteria range – criteria condition by date and year numbers YEAR($B$3:$B$12)=D3
- Type in )) to close the N and the SUMPRODUCT functions, and finish the formula
How
The SUMPRODUCT function’s ability to handle arrays without array formulas provides an advantage over the limitations of COUNTIF and COUNTIFS 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)=D3 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, conditional equations return Boolean values, and therefore need to be converted into numbers which are meaningful for the SUMPRODUCT function. We can do this in two ways:
- The N function
- Double minus (--)
The N function converts a value to a number. In this case, TRUE and FALSE results will become 1 and 0. On the other hand, double minus operator will only work for this purpose, to return numbers from Boolean values. Either will work for this example.
N(YEAR($B$3:$B$12)=D3) returns {1;1;1;1;1;1;1;0;0;0}
As a result both, formulas below return the same values.
=SUMPRODUCT(N(YEAR($B$3:$B$12)=D3))
=SUMPRODUCT(--(YEAR($B$3:$B$12)=D3))
