ExcelBanter - Time and Sum if

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Time and Sum if (https://www.excelbanter.com/excel-discussion-misc-queries/250166-time-sum-if.html)

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

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

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

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.

"Paul" wrote:

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.

"JBeaucaire" wrote:

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

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

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.

"Paul" wrote:

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

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.

--
Biff
Microsoft Excel MVP

"Paul" wrote in message
...
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.

"JBeaucaire" wrote:

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

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

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.

"Paul" wrote:

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

0.5 rather than 0.05, but otherwise correct, of course.
--
David Biddulph

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP

"Paul" wrote in message
...
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.

"JBeaucaire" wrote:

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

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

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.

"Paul" wrote:

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

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

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
0.5 rather than 0.05, but otherwise correct, of course.
--
David Biddulph

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP

"Paul" wrote in message
...
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.

"JBeaucaire" wrote:

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

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

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.

"Paul" wrote:

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

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
0.5 rather than 0.05, but otherwise correct, of course.
--
David Biddulph

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP

"Paul" wrote in message
...
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.

"JBeaucaire" wrote:

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

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

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.

"Paul" wrote:

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

.

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

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)

--
Biff
Microsoft Excel MVP

"Paul" wrote in message
...
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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
0.5 rather than 0.05, but otherwise correct, of course.
--
David Biddulph

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP

"Paul" wrote in message
...
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.

"JBeaucaire" wrote:

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

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

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.

"Paul" wrote:

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

.