Time and Sum if

  • Thread starter Thread starter Paul
  • Start date Start date
P

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
 
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.
 
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.
 
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>
 
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
 
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)
 
Back
Top