Dates and SumProducts

[Jeff Gross]

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.

 

[Roger Govier]

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

 

[Jeff Gross]

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