Sumproduct with date and time?
My data is:
Date Time Calls Offered 8/21/2007 10:00:00AM 2 8/21/2007 11:00:00AM 4 8/21/2007 11:30:00AM 2 8/21/2007 12:30:00PM 1 8/21/2007 1:00:00PM 2 8/21/2007 3:30:00PM 1 8/21/2007 4:00:00PM 1 etc. Note that the data is down to the 30min, but not all times are listed. Since I have to use the data across multiple weeks, I want to use sumproduct as a generic formula to grab numbers and put them into a pivotable table. My receiving tab is as such: A B C D Time 8/21/2007 8/22/2007 8/23/2007 etc 12:00:00AM 12:30:00AM 1:00:00AM 1:30:00AM 2:00:00AM 2:30:00AM 3:00:00AM 3:30:00AM 4:00:00AM etc. to 11:30pm I tried using: =SUMPRODUCT(--((Data!$B$2:$B$65500)=$B2),--((Data!$A$2:$A$65500)=$A2),(Data!$C$2:$C$65500)) Where the "Data" tab has dates in B2:B65500 and times in A2:A65500 and calls offered numbers in C2:C65500. When I use this formula I get zeroes even in cells that clearly have a number in there. I've tried different formats but still I get zero. Am I doing something wrong? |
All times are GMT +1. The time now is 12:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com