Thread: Dates and groups

Hi All,

I want to be able to work out how many people have a birthday in each month based on 2 criteria

In column A i have people's birthdays, in column B i have a category e.g. Red, Blue, Green

Is there any way that i can work out how many people have a birthday in August who are in the Green group?

It doesn't matter what year they were born in just the month.

Thanks in advance

Hello Smurfy:

The SUMPRODUCT() function should wrok for you.

Type these dates in cells D1 through O1, 1/1/1900, 2/1/1900, 3/1/1900... You can use custom formatting ( mmm ) so the dates look like so, Jan, Feb, Mar…

Type the colors in cells C2, C3 and C4 respectively.

Paste this formula in cell D2 then copy down and across to cell O4.

Code:

=SUMPRODUCT(--(MONTH($A$2:$A$1000)=MONTH(D$1)),--($B$2:$B$1000=$C2))