summing values in a data table based on criteria in another column
 

I have a data table whose columns are arranged in the following manner, from
left to right:

Date | ArriveTime | LeaveTime |Hours | Overtime | Regular | Gross |
RunningGross | CumHours | $/Week

What I'm looking to do is sum the "Gross" column based on a period of dates,
say 8/1/06 through 8/31/06, and not select this range manually.

The Date column is column D and the Gross column is column J.

Ideas?
--
Brevity is the soul of wit.

summing values in a data table based on criteria in another column
 

Hi Dave,

Something along this line :

=SUMIF(D2:C100,""& DATE(2006,7,31),J2:E100) -
SUMIF(D2:C100,""& DATE(2006,8,31),J2:E100)

HTH
Cheers
Carim

summing values in a data table based on criteria in another column
 

=SUMPRODUCT(--(D2:D100=StartDate),--(D2:D100<=End date),(J2:J100))

where start/End dates are cells containing these values.

If you want to sum a calendar month e.g August:

=SUMPRODUCT(--(MONTH(D2:D100)=8),J2:J100)

HTH

"Dave F" wrote:

I have a data table whose columns are arranged in the following manner, from
left to right:

Date | ArriveTime | LeaveTime |Hours | Overtime | Regular | Gross |
RunningGross | CumHours | $/Week

What I'm looking to do is sum the "Gross" column based on a period of dates,
say 8/1/06 through 8/31/06, and not select this range manually.

The Date column is column D and the Gross column is column J.

Ideas?
--
Brevity is the soul of wit.

summing values in a data table based on criteria in another co
 

Wow, that's great, thanks.

I ended up using the following:
=SUMPRODUCT(--(D13:D166=DATE(2006,7,1)),--(D13:D166<=DATE(2006,7,31)),(J13:J166))

Thanks.

Dave
--
Brevity is the soul of wit.


"Toppers" wrote:


=SUMPRODUCT(--(D2:D100=StartDate),--(D2:D100<=End date),(J2:J100))

where start/End dates are cells containing these values.

If you want to sum a calendar month e.g August:

=SUMPRODUCT(--(MONTH(D2:D100)=8),J2:J100)

HTH

"Dave F" wrote:

I have a data table whose columns are arranged in the following manner, from
left to right:

Date | ArriveTime | LeaveTime |Hours | Overtime | Regular | Gross |
RunningGross | CumHours | $/Week

What I'm looking to do is sum the "Gross" column based on a period of dates,
say 8/1/06 through 8/31/06, and not select this range manually.

The Date column is column D and the Gross column is column J.

Ideas?
--
Brevity is the soul of wit.

summing values in a data table based on criteria in another co
 

A better option might be to those dates into 2 cells and then just reference
the cells instead of hardcoding them into the formula like that. It's then
obvious at a glance as to what thye parameters of the query are.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Dave F" wrote in message
...
Wow, that's great, thanks.

I ended up using the following:
=SUMPRODUCT(--(D13:D166=DATE(2006,7,1)),--(D13:D166<=DATE(2006,7,31)),(J13:J166))

Thanks.

Dave
--
Brevity is the soul of wit.


"Toppers" wrote:


=SUMPRODUCT(--(D2:D100=StartDate),--(D2:D100<=End date),(J2:J100))

where start/End dates are cells containing these values.

If you want to sum a calendar month e.g August:

=SUMPRODUCT(--(MONTH(D2:D100)=8),J2:J100)

HTH

"Dave F" wrote:

I have a data table whose columns are arranged in the following manner,
from
left to right:

Date | ArriveTime | LeaveTime |Hours | Overtime | Regular | Gross |
RunningGross | CumHours | $/Week

What I'm looking to do is sum the "Gross" column based on a period of
dates,
say 8/1/06 through 8/31/06, and not select this range manually.

The Date column is column D and the Gross column is column J.

Ideas?
--
Brevity is the soul of wit.

summing values in a data table based on criteria in another co
 

Well those values are hard-coded, in the Dates column, however, when I enter
the dates in that manner I get a #VALUE! error. When I use the
DATE(2006,7,1) function I don't.
--
Brevity is the soul of wit.


"Ken Wright" wrote:

A better option might be to those dates into 2 cells and then just reference
the cells instead of hardcoding them into the formula like that. It's then
obvious at a glance as to what thye parameters of the query are.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------



"Dave F" wrote in message
...
Wow, that's great, thanks.

I ended up using the following:
=SUMPRODUCT(--(D13:D166=DATE(2006,7,1)),--(D13:D166<=DATE(2006,7,31)),(J13:J166))

Thanks.

Dave
--
Brevity is the soul of wit.


"Toppers" wrote:


=SUMPRODUCT(--(D2:D100=StartDate),--(D2:D100<=End date),(J2:J100))

where start/End dates are cells containing these values.

If you want to sum a calendar month e.g August:

=SUMPRODUCT(--(MONTH(D2:D100)=8),J2:J100)

HTH

"Dave F" wrote:

I have a data table whose columns are arranged in the following manner,
from
left to right:

Date | ArriveTime | LeaveTime |Hours | Overtime | Regular | Gross |
RunningGross | CumHours | $/Week

What I'm looking to do is sum the "Gross" column based on a period of
dates,
say 8/1/06 through 8/31/06, and not select this range manually.

The Date column is column D and the Gross column is column J.

Ideas?
--
Brevity is the soul of wit.

summing values in a data table based on criteria in another co
 

The following will work fine as long as the dates in Z1 and Z2 are real
dates:-

=SUMPRODUCT(--(D13:D166=$Z$1),--(D13:D166<=$Z$2),(J13:J166))

Regards
Ken........................


"Dave F" wrote in message
...
Well those values are hard-coded, in the Dates column, however, when I
enter
the dates in that manner I get a #VALUE! error. When I use the
DATE(2006,7,1) function I don't.
--
Brevity is the soul of wit.


"Ken Wright" wrote:

A better option might be to those dates into 2 cells and then just
reference
the cells instead of hardcoding them into the formula like that. It's
then
obvious at a glance as to what thye parameters of the query are.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Dave F" wrote in message
...
Wow, that's great, thanks.

I ended up using the following:
=SUMPRODUCT(--(D13:D166=DATE(2006,7,1)),--(D13:D166<=DATE(2006,7,31)),(J13:J166))

Thanks.

Dave
--
Brevity is the soul of wit.


"Toppers" wrote:


=SUMPRODUCT(--(D2:D100=StartDate),--(D2:D100<=End date),(J2:J100))

where start/End dates are cells containing these values.

If you want to sum a calendar month e.g August:

=SUMPRODUCT(--(MONTH(D2:D100)=8),J2:J100)

HTH

"Dave F" wrote:

I have a data table whose columns are arranged in the following
manner,
from
left to right:

Date | ArriveTime | LeaveTime |Hours | Overtime | Regular | Gross |
RunningGross | CumHours | $/Week

What I'm looking to do is sum the "Gross" column based on a period
of
dates,
say 8/1/06 through 8/31/06, and not select this range manually.

The Date column is column D and the Gross column is column J.

Ideas?
--
Brevity is the soul of wit.

summing values in a data table based on criteria in another co
 

Thanks, will try it.
--
Brevity is the soul of wit.


"Ken Wright" wrote:

The following will work fine as long as the dates in Z1 and Z2 are real
dates:-

=SUMPRODUCT(--(D13:D166=$Z$1),--(D13:D166<=$Z$2),(J13:J166))

Regards
Ken........................


"Dave F" wrote in message
...
Well those values are hard-coded, in the Dates column, however, when I
enter
the dates in that manner I get a #VALUE! error. When I use the
DATE(2006,7,1) function I don't.
--
Brevity is the soul of wit.


"Ken Wright" wrote:

A better option might be to those dates into 2 cells and then just
reference
the cells instead of hardcoding them into the formula like that. It's
then
obvious at a glance as to what thye parameters of the query are.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------



"Dave F" wrote in message
...
Wow, that's great, thanks.

I ended up using the following:
=SUMPRODUCT(--(D13:D166=DATE(2006,7,1)),--(D13:D166<=DATE(2006,7,31)),(J13:J166))

Thanks.

Dave
--
Brevity is the soul of wit.


"Toppers" wrote:


=SUMPRODUCT(--(D2:D100=StartDate),--(D2:D100<=End date),(J2:J100))

where start/End dates are cells containing these values.

If you want to sum a calendar month e.g August:

=SUMPRODUCT(--(MONTH(D2:D100)=8),J2:J100)

HTH

"Dave F" wrote:

I have a data table whose columns are arranged in the following
manner,
from
left to right:

Date | ArriveTime | LeaveTime |Hours | Overtime | Regular | Gross |
RunningGross | CumHours | $/Week

What I'm looking to do is sum the "Gross" column based on a period
of
dates,
say 8/1/06 through 8/31/06, and not select this range manually.

The Date column is column D and the Gross column is column J.

Ideas?
--
Brevity is the soul of wit.