SUM According to Date and Time

[IanGreeff]

Hi

I have to analyze our abandoned calls for the year. So far there are close to 2000 abandoned calls and I need analyze them according to the month and the time they were received.

I have two worksheets in my workbook the first worksheet is named DROPPED, this is were all the data is. The second worksheet is called DROPPED CALLS SUMMARY, this is where all the analysis happens.

On the DROPPED CALLS SUMMARY Worksheet I need to have the Sum of Column I IF ColumnA is equal to Jan-13(or whatever month) and Column B is equal to 8:00 AM (or whatever time). I'm using Excel 2003 so I cannot use the SUMIFS Formula and I can't find a formula to work with.




[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]MONTH[/TD]
[TD]DATE[/TD]
[TD]TIME[/TD]
[TD]ABANDONED CALLS[/TD]
[TD]ON QUEUE[/TD]
[TD]OUT OF QUEUE[/TD]
[TD]SEQUENCE[/TD]
[TD]VDN[/TD]
[TD]WORKING HOURS[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jan-13[/TD]
[TD]02/01/2013[/TD]
[TD]8:00 AM[/TD]
[TD]Abandoned on Q[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1080012[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jan-13[/TD]
[TD]03/01/2013[/TD]
[TD]8:00 AM[/TD]
[TD]Abandoned out of Q[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1080033[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jan-13[/TD]
[TD]03/01/2013[/TD]
[TD]8:00 AM[/TD]
[TD]Abandoned on Q[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1080033[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Feb-13[/TD]
[TD]01/02/2013[/TD]
[TD]5:00 PM[/TD]
[TD]Abandoned on Q[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1080015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Feb-13[/TD]
[TD]01/02/2013[/TD]
[TD]9:00 AM[/TD]
[TD]Abandoned out of Q[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1080009[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Feb-13[/TD]
[TD]02/02/2013[/TD]
[TD]9:00 PM[/TD]
[TD]Abandoned out of Q[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1080016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Feb-13[/TD]
[TD]02/02/2013[/TD]
[TD]8:00 AM[/TD]
[TD]Abandoned on Q[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1080005[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Mar-13[/TD]
[TD]01/03/2013[/TD]
[TD]8:00 AM[/TD]
[TD]Abandoned on Q[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1080005[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Mar-13[/TD]
[TD]01/03/2013[/TD]
[TD]10:00 AM[/TD]
[TD]Abandoned on Q[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1080032[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Mar-13[/TD]
[TD]01/03/2013[/TD]
[TD]10:00 AM[/TD]
[TD]Abandoned on Q[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1080023[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[DJBG1]

I think that what you need is a SUMPRODUCT:

SUMPRODUCT(--('Dropped!'A:A="Jan-13"),--('Dropped!'B:B="08:00"),C:C)

I think that should work!

 

[DJBG1]

Sorry, taking a look at your grid it would be:

SUMPRODUCT(--('Dropped!'A:A="Jan-13"),--('Dropped!'C:C="08:00"),'Dropped!'I:I)

 

[IanGreeff]

Sorry, taking a look at your grid it would be:

SUMPRODUCT(--('Dropped!'A:A="Jan-13"),--('Dropped!'C:C="08:00"),'Dropped!'I:I)

Thank you, but I am getting an Error when trying to use that formula.

 

[IanGreeff]

Just to let you know that Jan-13 is formatted it actually the actual date as it appears in Column B, the same with the time, it is formatted and the actual content is 08:00:00. I don't know if this needs to be considered in the formula.

 

[IanGreeff]

I have tried changing the cell range, I believe that with SUMPRODUCT and Excel 2003, you cannot specify a range as A:A but A1:A20. I tried it like this =SUMPRODUCT(--(DROPPED!A10:A65536="Jan-13"),--(DROPPED!C10:C65536="08:00 AM"),DROPPED!I10:I65536) but all it returns is 0 when infact according the data it should return 9.