Using a range of dates to add data in a different column?
 

I need to sum a range in a column based on the date in another column:
A B
1 Date Hours
2 05--Dec-26 7.2
3 05--Dec-29 6.8
4 06--Jan-01 2.1
5 06--Jan-12 3.3

essentially, i want to say "if the date in A is in Dec 05, add the
hours in B that correspond to Dec 05". so, in another cell, in another
sheet, i would get:

A B
1 Dec 05 14.0
2 Jan 06 5.4

B1 of the second sheet obviously where my formula would be.
Thanks.
-Carl Eubanks


--
Outraged
------------------------------------------------------------------------
Outraged's Profile: http://www.excelforum.com/member.php...o&userid=31491
View this thread: http://www.excelforum.com/showthread...hreadid=511703

Using a range of dates to add data in a different column?
 

Hi Outraged.

Assuming that the dates are not just labels try the following
Name the column of Dates as "Date"
Name the column of Hours as "Hours"

Use the Menu Tools Add-ins and check Analysis ToolPak
Enter the following in cell B1.
=SUM(IF(DateEOMONTH(A1,-1),IF(Date<=EOMONTH(A1,0),Hours,0),0))
Press [Ctrl][Shift][Enter] to accept
The formula will then look like
{=SUM(IF(DateEOMONTH(A1,-1),IF(Date<=EOMONTH(A1,0),Hours,0),0))}
This is an array function.
Copy this formula down as far as you need.

Regards

Phil Smith

"Outraged" wrote:


I need to sum a range in a column based on the date in another column:
A B
1 Date Hours
2 05--Dec-26 7.2
3 05--Dec-29 6.8
4 06--Jan-01 2.1
5 06--Jan-12 3.3

essentially, i want to say "if the date in A is in Dec 05, add the
hours in B that correspond to Dec 05". so, in another cell, in another
sheet, i would get:

A B
1 Dec 05 14.0
2 Jan 06 5.4

B1 of the second sheet obviously where my formula would be.
Thanks.
-Carl Eubanks


--
Outraged
------------------------------------------------------------------------
Outraged's Profile: http://www.excelforum.com/member.php...o&userid=31491
View this thread: http://www.excelforum.com/showthread...hreadid=511703