ExcelBanter - View Single Post

Claus Busch

Hi,

Am Mon, 8 Apr 2013 15:01:02 -0700 (PDT) schrieb :

A B c
1/2/2013 12:00 AM 3
1/2/2013 1:00 AM 4
1/2/2013 2:00 AM 1
1/2/2013 3:00 AM 6

I need a Sumproduct formula tha will add calls between 1:00 am and 3:00 am Only
Based on date and time look ups. I can not use the SUMIFS OR COUND IFS Because Im usint and external spreadsheet as my data source and it will not update unless you have the data spreadsheet open. So im stuck using a Sumproduct.

in D1 try:
=IF(A2=A1,"",SUMPRODUCT(--($A$1:$A$100=A1),--($B$1:$B$100=TIME(1,,)),--($B$1:$B$100<=TIME(3,,)),$C$1:$C$100))
and copy down. At the last value of the day you will get the result for
this day

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2