How to use Date range function in Sum(if....)
 

I have three rows as follows
Column A Column B
11-Jul-2006 $10
12-Jul-2006 $20
03-Aug-2006 $65

Now I want to calculate the monthly total in different rows.
For July, Its result would be $30
For Aug, Its result would be $65.

When I try the following formula, it is not working when I give the date range
SUM(IF(H41:H43DATE(2006,7,1)*(H41:H43<DATE(2006,7 ,31),I41:I43))

But if I give only one condition, either above the date or below the date it
is working fine. What is the way to enter the date ranges in the formula?

Thanks in Advance

How to use Date range function in Sum(if....)
 

Murugan, if you use a helper column, say C, in C2 you could enter
=Month(A2)-format as general-sort on Column C and then use Data-Subtotals
based on Column C. It's a fast and easy method to achieve what you want. HTH
--
Sincerely, Michael Colvin


"Murugan" wrote:

I have three rows as follows
Column A Column B
11-Jul-2006 $10
12-Jul-2006 $20
03-Aug-2006 $65

Now I want to calculate the monthly total in different rows.
For July, Its result would be $30
For Aug, Its result would be $65.

When I try the following formula, it is not working when I give the date range
SUM(IF(H41:H43DATE(2006,7,1)*(H41:H43<DATE(2006,7 ,31),I41:I43))

But if I give only one condition, either above the date or below the date it
is working fine. What is the way to enter the date ranges in the formula?

Thanks in Advance

How to use Date range function in Sum(if....)
 

Thanks.

Just found a way to do that without having helper column.
SUM(IF(MONTH(H41:H43)=7,I41:I43)

Thanks
"Michael" wrote:

Murugan, if you use a helper column, say C, in C2 you could enter
=Month(A2)-format as general-sort on Column C and then use Data-Subtotals
based on Column C. It's a fast and easy method to achieve what you want. HTH
--
Sincerely, Michael Colvin


"Murugan" wrote:

I have three rows as follows
Column A Column B
11-Jul-2006 $10
12-Jul-2006 $20
03-Aug-2006 $65

Now I want to calculate the monthly total in different rows.
For July, Its result would be $30
For Aug, Its result would be $65.

When I try the following formula, it is not working when I give the date range
SUM(IF(H41:H43DATE(2006,7,1)*(H41:H43<DATE(2006,7 ,31),I41:I43))

But if I give only one condition, either above the date or below the date it
is working fine. What is the way to enter the date ranges in the formula?

Thanks in Advance

How to use Date range function in Sum(if....)
 

or with Sumproduct

=SUMPRODUCT(--(MONTH(H41:H43)=7)*((I41:I43)))


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568532