SUMPRODUCT

[Jim]

Hello Excel Yoda’s,

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

 

[DOR]

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