Excel counts withing specific date ranges (star signs)

[beeso74]

What I'm basically trying to do is to count how many people have coded 1 through to 10 within each star sign - regardless of year born.
I've tried varies formulas from looking around the internet, but none seem to work!

Any help on the enclosed xls would be great!

Many thanks

[daddylonglegs]

I created a lookup table in X1:Y15 which shows the start day/month for each star sign (Capricorn has to appear twice, once for the start of the year once for the end).

Then in your headers I1:T1 I listed just the star sign, then you can use this formula in I2 copied across and down

=SUMPRODUCT((LOOKUP(MONTH($A$2:$A$2000)*100+DAY($A$2:$A$2000),$X$2:$Y$15)=I$1)*($B$2:$B$2000=$H2))

U11 is just a check - it counts numbers in column B - that should match the sum in U12 to check that all entries are counted

Do the same for the other answers just changing the red part to the correct column, see attached

[daddylonglegs]

....another way that might be simpler is to use an extra column to get the star sign for each date, e.g. insert column at B and use this formula in B2

=LOOKUP(MONTH(A2)*100+DAY(A2),Y$2:Z$15)

copy that down the column and you get the star sign against ecah date, then just a simple COUNTIFS in J2 copied across and down

=COUNTIFS($B:$B,J$1,$C:$C,$I2)

see revised attachment

[beeso74]

Brilliant daddylonglegs! Much appreciated