Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting the occurances for specific dept within a given time peri
I'm trying to count the number of occurances by department that fall within a
specific date range(in this case monthly....ie 08/01/07 - 08/31/07) The formula I'm using is as follows: =COUNTIF('Filled Reqs'!$R$2:$R$389,"08/01/07")- COUNTIF('Filled Reqs'!$R$2:$R$389,"<'09/01/07") ** this gives me the count within a given month =COUNTIF('Filled Reqs'!$R$2:$R$389,"08/01/07")-COUNTIF('Filled Reqs'!$R$2:$R$389,"<'09/01/07")*AND('Filled Reqs'!$B$2:$B$389,"legal")*AND('Filled Reqs'!$o$2:$o$389,"analyst") *** this doesn't work.... count within given month by department[/b] |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting the occurances for specific dept within a given time peri
Try this.
=SUMPRODUCT(--('Filled Reqs'!$R$2:$R$389=DATE(2007,8,1)),--('Filled Reqs'!$R$2:$R$389<=DATE(2007,8,31)),--('Filled Reqs'!$O$2:$O$389="analyst")) If the dates are all from the same year then you can just count based on the month number: =SUMPRODUCT(--(MONTH('Filled Reqs'!$R$2:$R$389)=8),--('Filled Reqs'!$O$2:$O$389="analyst")) Note that when counting based on the month number an empty cell will evaluate as month 1 (January). Better to use cells to hold the criteria: A1 = 8/1/2007 B1 = 8/31/2007 C1 = analyst =SUMPRODUCT(--('Filled Reqs'!$R$2:$R$389=A1),--('Filled Reqs'!$R$2:$R$389<=B1),--('Filled Reqs'!$O$2:$O$389=C1)) -- Biff Microsoft Excel MVP "Ralph D'Andrea" <Ralph wrote in message ... I'm trying to count the number of occurances by department that fall within a specific date range(in this case monthly....ie 08/01/07 - 08/31/07) The formula I'm using is as follows: =COUNTIF('Filled Reqs'!$R$2:$R$389,"08/01/07")- COUNTIF('Filled Reqs'!$R$2:$R$389,"<'09/01/07") ** this gives me the count within a given month =COUNTIF('Filled Reqs'!$R$2:$R$389,"08/01/07")-COUNTIF('Filled Reqs'!$R$2:$R$389,"<'09/01/07")*AND('Filled Reqs'!$B$2:$B$389,"legal")*AND('Filled Reqs'!$o$2:$o$389,"analyst") *** this doesn't work.... count within given month by department[/b] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Occurances | Excel Discussion (Misc queries) | |||
counting occurances | Excel Worksheet Functions | |||
Find name in list and get dept # enter dept # on work sheet | Excel Worksheet Functions | |||
counting instances of specific times in fields with date and time | Excel Worksheet Functions | |||
Counting multiple occurances of a specific string | Excel Worksheet Functions |