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

  • Thread starter Thread starter Louisa
  • Start date Start date
L

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?
 
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
 
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
 
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
 
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
 
Back
Top