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

[Guest]

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?

 

[Carim]

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

 

[Guest]

=SUMPRODUCT(--(D2100>=StartDate),--(D2100<=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(D2100)=8),J2:J100)

HTH

 

[Guest]

Wow, that's great, thanks.

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

Thanks.

Dave

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Guest]

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.

 

[Ken Wright]

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

=SUMPRODUCT(--(D13166>=$Z$1),--(D13166<=$Z$2),(J13:J166))

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

 

[Guest]

Thanks, will try it.