SumProduct Help

[mralmackay]

I need to do the following which I had crudely done before but now as
a result of needing to be 'cleverer' with what we're reporting, I now
need to change it slightly.

Have data in cells A45 which comes from an external source via an
ODBC connection. In particular I'm only interested in columns B,J,K
and P (see example of data below).

What I need to be able to do is :
Have a table, by Month, that has four columns (P1: In SLA, P1: Out
SLA, P2: In SLA, P2: Out SLA)

If my table began in another sheet on A1, what I'd want is:
Column A: Contains all the months (and a1 would be a title of month)
B1: Need a formula = Count of all examples within January where the
SLA Priority (Column P) is (1 and difference in hours between create
and close is <1 hour)

I did a formula before but wasn't sure how to limit this by month as
well? And also this worked by way of a Helper column to work out the
difference which I'd prefer to get rid of if possible? Formula was
using Sumproduct:
=SUMPRODUCT((P547="1")*(Q5:Q47<1))

COLUMN: B COLUMN: J COLUMN: K COLUMN: P
Case-ID Create-Date Closed-Date SLA-Priority
0000090033 19/01/2008 08:18 19/01/2008 08:18 3
0000090038 19/01/2008 08:46 19/01/2008 12:27 1
0000090044 19/01/2008 09:01 20/01/2008 14:26 3
0000090047 19/01/2008 09:08 19/01/2008 12:52 1
0000090049 19/01/2008 09:13 19/01/2008 09:56 2
0000090081 19/02/2008 10:04 19/02/2008 10:23 3
0000090088 19/02/2008 10:12 19/02/2008 15:37 2
0000090090 19/02/2008 10:13 20/02/2008 14:13 1
0000090098 19/02/2008 10:23 19/02/2008 13:30 6
0000090125 19/02/2008 10:59 19/02/2008 10:59 2
0000090131 19/02/2008 11:12 20/02/2008 08:26 1
0000090135 19/02/2008 11:24 20/02/2008 16:46 2

Appreciate your help on this.

Thanks, al.

 

[Pete_UK]

Try something like this in B1 of your new sheet (assume existing sheet
is called Sheet1):

=SUMPRODUCT((TEXT(Sheet1!$J$5:$J$47,"mmmm")=$A1)*(Sheet1!$P$5:$P
$47="1")*((Sheet1!$K$5:$K$47-Sheet1!$J$5:$J$47)<1))

I think you then want to copy this across into other cells on the same
row, but you will need to change the "1" in the middle of the formula
to the other values (in your sample data these are "1", "2", "3" and
"6", but I'm not sure how these relate to your P1 and P2 values in
your description). Also if these are real numbers in column P then you
will have to omit the quotes.

Then you can copy these cells down to row 12 for the other months.

Hope this helps.

Pete