counting the occurances for specific dept within a given time peri

[Ralph D'Andrea]

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]

 

[T. Valko]

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