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

[Murugan]

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:H43>DATE(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

[Michael]

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:H43>DATE(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
>
>
>

[Murugan]

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:H43>DATE(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
> >
> >
> >

[VBA Noob]

or with Sumproduct

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