Sumproduct Help!

J

jc9972003

Hello I need help with a Sumproduct formula.

I have a column with dates another with times and the last one with number of calls
Example
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.
 
C

Claus Busch

Hi,

Am Mon, 8 Apr 2013 15:01:02 -0700 (PDT) schrieb (e-mail address removed):
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
 
K

Kevin@Radstock

May be!
=SUMPRODUCT(--(A1:A4=A1),--(B1:B4>=1/24),--(B1:B4<=3/24),C1:C4)

Or you can use cell reference to refer to a time & dates in cells.
 
J

jc9972003

Hello I need help with a Sumproduct formula. I have a column with dates another with times and the last one with number of calls Example 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.



Thank you Claus
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top