Time and Sum if

[Paul]

I have a data logger that creates a spreadsheet. In one of the fields is the
date and time in 10 min increments, the other field is the pump output. I
want to be able to do two things.

First is add all of the pump output from say 9am to 9pm on a given day
Second, I want to be able to add all of the pump output for a given day.

I need to do this calcuation hundreds of time, so I am hoping there is a
sumif that will work, I just cannot seem to make it work.

thanks

 

[JBeaucaire]

You will most likely be using a SUMPRODUCT() formula for these tasks. Read
through the complete primer found here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#classic

 

[Paul]

I think this might be helpful, and I am pretty well versed at figuring these
things out, but I cannot for the life of me figure out how to 'pull' the time
our of field that had both time and date information.

 

[T. Valko]

To separate the time from a date/time:

A1 = 1/1/2009 12:00 PM (as a true Excel date/time)

=MOD(A1,1)

Formatted as General or Number the result is 0.05.

Formatted as Time the result is 12:00 PM.

To separate the date from a date/time:

=INT(A1)

Formatted as General or Number the result is 39814.

Formatted as Date the result is 1/1/2009.

 

[T. Valko]

0.5 rather than 0.05

It was late and my vision was blurred. The 22 beers I drank didn't have
anything to do with it! <g>

 

[Paul]

Thanks for the start. The next part is how to use it in a formula that will
sumif a criteria is met.

So for arguments sake, if the date/time is in Column A, and the item I want
to sum is in Column B, I want to be able to ask these two questions (in Excel
2003):

1) Tell me the sum of column B where column A is equal to a certain date

=Sumif(A:A, Criteria, B:B)

The challenge is that column A is a Date/Time field and the criteria is
date. So what I need is to say if the Date is >=date and <date+1. It is
establishing a criteria for the sumif that is my challenge. If sumif is not
the right tool that would be fine, but then what is and how do I apply it.

Thanks much.

Paul

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Filter for date(s) and time slice(s)
Source is compound date+time.
With auto refresh.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_05_09a.xlsm
http://www.mediafire.com/file/jfnxf5unyij/12_05_09a.xlsm
Will not play in Excel 2003.

 

[T. Valko]

You don't necessarily need to separate the date/time to do this.

Here are a few examples.

A2:A20 = true Excel date/times
B2:B20 = values to sum

To sum for a specific single date:

D2 = some date like 1/1/2009

=SUMPRODUCT(--(INT(A2:A20)=D2),B2:B20)

To sum for a range of dates:

D2 = start date like 1/1/2009
E2 = end date like 1/5/2009

=SUMPRODUCT(--(INT(A2:A20)>=D2),--(INT(A2:A20)<=E2),B2:B20)

To sum for a specific single date within a specific time range:

D2 = some date like 1/1/2009
E2 = start time like 9:00 AM
F2 = end time like 9:00 PM

=SUMPRODUCT(--(A2:A20>=D2+E2),--(A2:A20<=D2+F2),B2:B20)