Dates and SumProducts
 

I have ~3000 lines of data. Column D includes an incident date for data
collected over the entire current year. Column E includes an incident type
(there are ~20 varieties). Column F includes the incident status (3 types).
I need to come up with a formula (or multiple) to count the number of times a
specific incident type occurs within the current month for each of the three
incident status types (APPR, CLOS, SUBM). I was expecting to have a formula
for each status type and each variety of incidents. I also have to do the
same counting for the current week.

When I was doing the year to date information I used a sumproduct as follows:

=SUMPRODUCT(($E$35:$E$1000="PROPERTY DAMAGE/VANDALISM
")*($F$35:$F$1000="APPR "))

Any help would be appreciated.

Thanks in advance.

Dates and SumProducts
 

Hi Jeff

I would use different cells to hold the 3 types, say in M1:O1
In L2 enter the month you want in the form 01 Aug 2008
In L3 Enter the start of the week you want 04 Aug 2008

In M2 enter
=SUMPRODUCT(
($E$35:$E$1000="PROPERTY DAMAGE/VANDALISM ")*
($F$35:$F$1000=M$1))*
(TEXT($D$35:$D$1000,"yymm")=TEXT($L2),"yymm"))
Copy across through N2:O2

In M3 enter

=SUMPRODUCT(
($E$35:$E$1000="PROPERTY DAMAGE/VANDALISM ")*
($F$35:$F$1000=M$1))*
(TEXT($D$35:$D$1000,"yymmdd")=TEXT($L3),"yymmdd") *
(TEXT($D$35:$D$1000,"yymmdd")<=TEXT($L3+6),"yymmdd "))

Copy across through N3:O3

--
Regards
Roger Govier

"Jeff Gross" wrote in message
...
I have ~3000 lines of data. Column D includes an incident date for data
collected over the entire current year. Column E includes an incident
type
(there are ~20 varieties). Column F includes the incident status (3
types).
I need to come up with a formula (or multiple) to count the number of
times a
specific incident type occurs within the current month for each of the
three
incident status types (APPR, CLOS, SUBM). I was expecting to have a
formula
for each status type and each variety of incidents. I also have to do the
same counting for the current week.

When I was doing the year to date information I used a sumproduct as
follows:

=SUMPRODUCT(($E$35:$E$1000="PROPERTY DAMAGE/VANDALISM
")*($F$35:$F$1000="APPR "))

Any help would be appreciated.

Thanks in advance.

Dates and SumProducts
 

I apologize but I forgot to mention a few things. First, the data is
downloaded from a website. Second, the data in my spreadsheet is from a
MSQuery to the downloaded data so I have no control on using the different
cells for the three types.

Thanks for your help.

Jeff


"Roger Govier" wrote:

Hi Jeff

I would use different cells to hold the 3 types, say in M1:O1
In L2 enter the month you want in the form 01 Aug 2008
In L3 Enter the start of the week you want 04 Aug 2008

In M2 enter
=SUMPRODUCT(
($E$35:$E$1000="PROPERTY DAMAGE/VANDALISM ")*
($F$35:$F$1000=M$1))*
(TEXT($D$35:$D$1000,"yymm")=TEXT($L2),"yymm"))
Copy across through N2:O2

In M3 enter

=SUMPRODUCT(
($E$35:$E$1000="PROPERTY DAMAGE/VANDALISM ")*
($F$35:$F$1000=M$1))*
(TEXT($D$35:$D$1000,"yymmdd")=TEXT($L3),"yymmdd") *
(TEXT($D$35:$D$1000,"yymmdd")<=TEXT($L3+6),"yymmdd "))

Copy across through N3:O3

--
Regards
Roger Govier

"Jeff Gross" wrote in message
...
I have ~3000 lines of data. Column D includes an incident date for data
collected over the entire current year. Column E includes an incident
type
(there are ~20 varieties). Column F includes the incident status (3
types).
I need to come up with a formula (or multiple) to count the number of
times a
specific incident type occurs within the current month for each of the
three
incident status types (APPR, CLOS, SUBM). I was expecting to have a
formula
for each status type and each variety of incidents. I also have to do the
same counting for the current week.

When I was doing the year to date information I used a sumproduct as
follows:

=SUMPRODUCT(($E$35:$E$1000="PROPERTY DAMAGE/VANDALISM
")*($F$35:$F$1000="APPR "))

Any help would be appreciated.

Thanks in advance.