Count the number of times a specific date appears in a range of ce

[Louisa]

I need to track contract end dates for staff at three centres, my spreadsheet
looks like this:

centre end date
TJAS 1/10/10
ACT 1/9/10
ACB 1/12/10

I need a summary table that shows how many staff from each centre have a
contract end date in a particular month e.g.
TJAS ACT ACB
January 1 3 8

is there a formula i could use that would work this out for me?

 

[Jacob Skaria]

Suppose you have your data in Sheet1 ColA and ColB with centre and end dates
A1: B100 with headers in row1.

In Sheet2 you have your layout like the below with headers in row 1
Month TJAS act
Jan-09 = =
Feb-09 = =
Mar-09 = =
Apr-09 = =
May-09 = =

In B2 enter the below formula
=SUMPRODUCT(--(Sheet1!$A$2:$A$10=B$1),--(MONTH(Sheet1!$B$2:$B$10)=MONTH($A2)))

Copy that to C2 and the subsequent rows/columns. Make sure the centre in
header is mentioned exactly same as what is in sheet1


If this post helps click Yes

 

[Jacob Skaria]

Suppose you have your data in Sheet1 ColA and ColB with centre and end dates
A1: B100 with headers in row1.

In Sheet2 you have your layout like the below with headers in row 1
Month TJAS act
Jan-09 = =
Feb-09 = =
Mar-09 = =
Apr-09 = =
May-09 = =

In B2 enter the below formula
=SUMPRODUCT(--(Sheet1!$A$2:$A$10=B$1),--(MONTH(Sheet1!$B$2:$B$10)=MONTH($A2)))

Copy that to C2 and the subsequent rows/columns. Make sure the centre in
header is mentioned exactly same as what is in sheet1


If this post helps click Yes

 

[Louisa]

Hi Jacob,

Thanks for the input but it is just returning #value in the cell. I have
formatted all of my date cells in the style you suggested below and i have
checked that my centre names are spelt identically in all places, is there
something else that i am doing wrong?

Louisa

 

[Louisa]

Hi Jacob,

Thanks for the input but it is just returning #value in the cell. I have
formatted all of my date cells in the style you suggested below and i have
checked that my centre names are spelt identically in all places, is there
something else that i am doing wrong?

Louisa

 

[Jacob Skaria]

In Sheet2 check whether the month entries are formatted as date

 

[Jacob Skaria]

In Sheet2 check whether the month entries are formatted as date