Let’s see how to combine the SUMPRODUCT and TEXT functions in Excel to find out the number of birthdays in a particular month.
Table of Contents
Formula
We have a list of Date of Births in the cells C2:C17. The following formula will return the number of birthdays in the month of July.
=SUMPRODUCT(--(TEXT($C$2:$C$17,"MMMM")="July"))
Explanation
SUMPRODUCT functions multiplies the arrays specified in it and returns the ‘sum of products’ as result. If a single array is used inside it, SUMPRODUCT function will return the sum of values in that array.
TEXT function can extract the corresponding Month from an Excel date. Here, when the range C3:C18 is used inside TEXT function, the expression evaluates to an array of Months.
=SUMPRODUCT(--(TEXT($C$3:$C$18,"MMMM")="July"))
becomes
=SUMPRODUCT(--({"December";"July";"November";"March";"January";"August";"October";"June";"January";"July";"February";"January";"April";"April";"November";"July"})="July"))
Then the array of months are compared to July to produce an array of TRUE and FALSE values.
=SUMPRODUCT(--({FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}))
These TRUE and FALSE values are then coerced into 1 (Ones) and 0 (Zeros) using ‘– –‘ double negative (also called double unary). The resulting array will be like,
=SUMPRODUCT({0;1;0;0;0;0;0;0;0;1;0;0;0;0;0;1})
Finally, SUMPRODUCT function will return the sum of values in the array, which is 3 in this case.
Formula with cell reference
In the above example, we have used the direct value ‘July’ in the formula. Instead of hard coding the value in the formula, we can use the address of the cell that contains the text to search for.
The following formula returns the number of birthdays of the month present in the cell E10.
=SUMPRODUCT(--(TEXT($C$2:$C$17,"MMMM")=E10))
Formula using MONTH funtion
We can use MONTH function to extract the numerical equivalent of a month from a date. These numbers are then compared to the numerical equivalent of the required month. For example, 7 is the number corresponding to July. So the formula to find the count of birthdays in July becomes
=SUMPRODUCT(--(MONTH($C$2:$C$17)=7))
