Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct Help
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 A4:P5 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((P5:P47="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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct Help
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 On Aug 6, 9:37*pm, wrote: 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 A4:P5 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((P5:P47="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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct Help | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Help with SUMPRODUCT | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions |