How do I find the number of peole born in the month of April using an excel formula?

Hi,

I may be missing something here but this counts the April dates in Col B

=SUMPRODUCT((MONTH(B2:B1000)=4)*(B2:B1000<>""))

Change the 4 to (say) 12 for December

heyy Mike,

Can you please help me understand this:

why  =SUMPRODUCT((MONTH(B1:B100)=4)*1) gives the right answer

whereas =SUMPRODUCT(MONTH(B1:B100)=4) does not?   :/

Because SUMPRDUCT works on a single array to!

Hi,

The reason the first doesn't work is because SUMPRODUCT likes to work with numbers and this formula

=SUMPRODUCT(MONTH(B1:B100)=4)

is returning an ARRAY like this so sumproduct can't add then up

=SUMPRODUCT({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})

However in your second formula the *1 coerces all the TRUE and FALSE values into 1 and zero like this.

=SUMPRODUCT({1;1;0;0;0;0;0;0;0;0})

Hope that clears it up. However; once again, I wouldn't use either because if you test for January and there are blank cells in the range those blanks will evaluate as January birthdates.

I would use this one which tests for blanks in the range and because we're multiplying 2 TRUE FALSE arrays then that multiplication coerces the TRUE/FALSE into 1 and zero.

=SUMPRODUCT((MONTH(B2:B1000)=4)*(B2:B1000<>""))