Count dates in given year - KING OF EXCEL

Friday, December 20, 2019

Count dates in given year

Count dates in given year

Excel formula: Count dates in given year
Generic formula 
To count dates in a given year, you can use the SUMPRODUCT and YEAR functions. In the example shown, the formula in E5 is:

where "dates" the a named range B5:B15.

How this formula works

The YEAR function extracts the year from a valid date. In this case, we are giving YEAR and array of dates in the named range "dates", so we get back an array of results:

Each date is compared to the year value in column D, to produce an array or TRUE FALSE values:

For the formula in E5, TRUE values are cases where dates are in the year 2017, and FALSE values represent dates in any other year.
Next, we use a double negative to coerce the TRUE FALSE values to 1's and 0's. Inside SUMPRODUCT, we now have:

Finally, with only one array to work with, SUMPRODUCT sums the items in the array and returns a result, 3.
#evba #etipfree
📤You download App installed directly on the latest phone here :

No comments:

Post a Comment