Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMPRODUCT
Hello Excel Yodas,
SUMPRODUCT question. Sheet One: I am dumping call data (includes times of calls and store numbers). F G H I 101 8:39:06 Keys 2 101 8:59:29 Keys 2 101 9:14:55 Keys 2 101 9:15:18 Keys 2 101 9:52:53 Keys 0 101 10:29:33 Keys 2 101 10:45:37 Keys 2 101 10:53:28 Keys 1 101 10:54:08 Keys 2 101 10:57:27 Keys 1 Sheet Two: I have the store numbers and names A B Store Name 1 101 Store Name 2 105 Store Name 3 106 Store Name 4 107 Store Name 5 108 Store Name 6 109 Sheet Three: This is where I want to format the data from both sheets (but as you can see I am receiving an error from my formula). A B C D E F G Sales Sales Sales Sales 00:00-00:29 00:30-00:59 01:00-1:29 1:30-1:59 Store Name 1 101 #NUM! Store Name 2 105 Store Name 3 106 Store Name 4 107 Store Name 5 108 Store Name 6 109 I have successfully referenced the store numbers and names from sheet two into columns A and B. Now what I would like to do is record the time calls came into the office by half hour. In row 1 I have half hour increments from C:ES. There are so many because I am referencing Sales (1), Service (2) and 0 prompts. I am referencing in the formula B# for the store number lookup to sheet two. Here is the formula I am using: =SUMPRODUCT(--(€˜Sheetone!$G:$G=TIME(0,0,0)),--( €˜Sheetone!$G:$G<=TIME(0,29,59)),--( €˜Sheetone!$I:$I=1),--( €˜Sheetone!$F:$F=B3)) Your help is appreciated. Thanks |
#2
|
|||
|
|||
SUMPRODUCT
You should consider using two helper rows, let's say rows 2 and 3. Put the
start of each half hour in row 2 and the end in row 3, so in the first column (C), C2 would have 00:00, and C3 would have 00:30, D2 would have 00:30 and D3 would have 1:00, and so on. You can hide one of these and use the other as the indicator for each half hour or you can concatenate them in another row, as you originally had, an hide them both. Now your SUMPRODUCT is easier: =SUMPRODUCT(--(sheet1!G1:G1000=C$2),--(sheet1!G1:G1000<C$3),--(sheet1!F1:F1000=$B4),--(sheet1!I1:I1000=1)) Note that the sumproduct ranges cannot cannot be entire columns, as you had them. Copy/drag over and down. You might also consider moving your time columns over one column to the right by inserting a column before the current column C and using it for the type of call. It can then be a variable referenced by the formula. Then, instead of hardcoding the type of call (0,1,2) into the formula, you can have a reference to it in column C (after moving everything over a column!) and the relevant term in your SUMPRODUCT would be --(sheet1!I1:I1000=$C3) Now your matrix is deep rather than wide, and you can ultimately handle new call types without changing your formulas. You can group then your calls by type vertically, rather than horizontally. You know, sure as night follows day, that as soon as you have this working, you will have new call types, and a spreadsheet is a lot deeper than it is wide, so you are probably better off expanding downwards rather than to the right! Fortunately, the day is limited to 48 half hours and the most that happen in that respect is a need to summarize in quarter hours rather than half hours. This approach facilitates that also, by simply using quarter hour intervals at the top and expanding to the right. Indeed you should probably calculate each interval start and end by adding the appropriate quantity (half hour at this point) but store it somewhere in your worbook as a parameter that you can change. Hope this helps. Declan O'R "Jim" wrote: Hello Excel Yodas, SUMPRODUCT question. Sheet One: I am dumping call data (includes times of calls and store numbers). F G H I 101 8:39:06 Keys 2 101 8:59:29 Keys 2 101 9:14:55 Keys 2 101 9:15:18 Keys 2 101 9:52:53 Keys 0 101 10:29:33 Keys 2 101 10:45:37 Keys 2 101 10:53:28 Keys 1 101 10:54:08 Keys 2 101 10:57:27 Keys 1 Sheet Two: I have the store numbers and names A B Store Name 1 101 Store Name 2 105 Store Name 3 106 Store Name 4 107 Store Name 5 108 Store Name 6 109 Sheet Three: This is where I want to format the data from both sheets (but as you can see I am receiving an error from my formula). A B C D E F G Sales Sales Sales Sales 00:00-00:29 00:30-00:59 01:00-1:29 1:30-1:59 Store Name 1 101 #NUM! Store Name 2 105 Store Name 3 106 Store Name 4 107 Store Name 5 108 Store Name 6 109 I have successfully referenced the store numbers and names from sheet two into columns A and B. Now what I would like to do is record the time calls came into the office by half hour. In row 1 I have half hour increments from C:ES. There are so many because I am referencing Sales (1), Service (2) and 0 prompts. I am referencing in the formula B# for the store number lookup to sheet two. Here is the formula I am using: =SUMPRODUCT(--(€˜Sheetone!$G:$G=TIME(0,0,0)),--( €˜Sheetone!$G:$G<=TIME(0,29,59)),--( €˜Sheetone!$I:$I=1),--( €˜Sheetone!$F:$F=B3)) Your help is appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |