Count the number of times a specific date appears in a range of ce
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? |
Count the number of times a specific date appears in a range of ce
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 "Louisa" wrote: 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? |
Count the number of times a specific date appears in a range o
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" wrote: 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 "Louisa" wrote: 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? |
Count the number of times a specific date appears in a range o
In Sheet2 check whether the month entries are formatted as date
-- If this post helps click Yes --------------- Jacob Skaria "Louisa" wrote: 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" wrote: 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 "Louisa" wrote: 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? |
All times are GMT +1. The time now is 01:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com