Hi,

I need a formula to count the number events that occur BY a certain person
(in column B, with each person numbered from 1-22), IN a certain category (in
column C, with the events coded as A,B,C and D) AND in a certain month (in
column E, date format ??/??/????)

Any ideas?

=SUMPRODUCT(--(B2:B20=1),--(C2:C20="A"),--(MONTH(E2:E20)=3))

--
__________________________________
HTH

Bob

"Ghalmaraz" wrote in message
...
Hi,

I need a formula to count the number events that occur BY a certain person
(in column B, with each person numbered from 1-22), IN a certain category
(in
column C, with the events coded as A,B,C and D) AND in a certain month (in
column E, date format ??/??/????)

Any ideas?

Hi,
Try this (example for Person Nr.1, Category "A" and Month February):
=SUMPRODUCT((B1:B1000=1)*(C1:C1000="A")*(MONAT(E1: E1000)=2))


"Ghalmaraz" wrote:

Hi,

I need a formula to count the number events that occur BY a certain person
(in column B, with each person numbered from 1-22), IN a certain category (in
column C, with the events coded as A,B,C and D) AND in a certain month (in
column E, date format ??/??/????)

Any ideas?

Thanks Bob, That worked a treat! Is there any way to adapt it so it also
screens out for year as well as month?

"Bob Phillips" wrote:

=SUMPRODUCT(--(B2:B20=1),--(C2:C20="A"),--(MONTH(E2:E20)=3))

--
__________________________________
HTH

Bob

"Ghalmaraz" wrote in message
...
Hi,

I need a formula to count the number events that occur BY a certain person
(in column B, with each person numbered from 1-22), IN a certain category
(in
column C, with the events coded as A,B,C and D) AND in a certain month (in
column E, date format ??/??/????)

Any ideas?

Not to worry, I figured it out - its ends up like this -

=SUMPRODUCT(--('Access Data'!B2:B4000=1),--('Access
Data'!C2:C4000="A"),--(MONTH('Access Data'!E2:E4000)=1),--(YEAR('Access
Data'!E2:E4000)=1999))

Thanks for your help!
"Ghalmaraz" wrote:

Thanks Bob, That worked a treat! Is there any way to adapt it so it also
screens out for year as well as month?

"Bob Phillips" wrote:

=SUMPRODUCT(--(B2:B20=1),--(C2:C20="A"),--(MONTH(E2:E20)=3))

--
__________________________________
HTH

Bob

"Ghalmaraz" wrote in message
...
Hi,

I need a formula to count the number events that occur BY a certain person
(in column B, with each person numbered from 1-22), IN a certain category
(in
column C, with the events coded as A,B,C and D) AND in a certain month (in
column E, date format ??/??/????)

Any ideas?

Sorry, there was an error in the formula,
Here is one, that also screens out the year:
Example for Person 1, Cat A, Month 2, Year 2009

=SUMPRODUCT((B1:B1000=1)*(C1:C1000="A")*(E1:E1000 =DATE(2009,2,1))*(E1:E1000<=DATE(2009,3,0)))

Rgds. Hansueli