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]
|