How to Count Cells in A Year

The SUMPRODUCT function is to return the sum of the products of corresponding ranges or arrays. It can also count the cells in a certain year, a month, or a day. The following formula is to figure out the number of cells in a year.

Example: You are working with a dataset with the first name in column A, the last name in column B, and the DOB (date of birth) in column C.

Formula 1: To count the number of people that were born in 1992

=SUMPRODUCT(--(YEAR(C2:C12)=1992))

  • YEAR(C2:C12)=1992: if DOB is in 1992, it returns true, otherwise false;
  • The double hyphens (--) convert true into 1, and false into 0;
  • The SUMPRODUCT returns the sum of the products of the array.

The result returns 3, and three people were born in 1992. You can also use the COUNTIFS function.

Formula 2: To count the number of people that were born in 1994

=COUNTIFS(C2:C12,">=1/1/1994",C2:C12,"<=12/31/1994")

The result returns 2, and two people were born in 1994.

Formula 3: If you need to change the dates to the reference cells, and G1 is the start date and H1 is the end date, you can use the following formula:

=COUNTIFS(C2:C12,">="&G1,C2:C12,"<="&H1)

The result returns 2, and two people were born in 1994.

Notes: The SUMPRODUCT function

The SUMPRODUCT function adds all the multiplication results for all arrays.

Formula:

=SUMPRODUCT(array1, [array2], …)

Explanations:

– Array1 is required, the first array is to multiply and then add.
– Array2 is optional, the second array is to multiply and then add.

Leave a Reply