Thread: Sum between a range of dates

I want to get the sum between a range of dates.

H8:S8 contain the dates
H11:S11 contains the values
V5 is the start date
V6 is the end date

I have tried the following as an array formula but if only works if V5 and
V6 are at least that of range H8:S8

=SUM(IF(AND(H8:S8>=V5,H8:S8<=V6),H11:S11))

In other words I want it to sum the cell the do meet the criteria.

Any ideas?

=SUMIF($H$8:$S$8,">="&V5,$H$11:$S$11)-SUMIF($H$8:$S$8,">"&V6,$H$11:$S$11)

which just needs enter.

Just a comment: The array formula you tried invokes AND() which is not
appropriate for it can just return a single value, not an array. For the
latter you need to use the multiplication operator.

Bruce wrote:
> I want to get the sum between a range of dates.
>
> H8:S8 contain the dates
> H11:S11 contains the values
> V5 is the start date
> V6 is the end date
>
> I have tried the following as an array formula but if only works if V5 and
> V6 are at least that of range H8:S8
>
> =SUM(IF(AND(H8:S8>=V5,H8:S8<=V6),H11:S11))
>
> In other words I want it to sum the cell the do meet the criteria.
>
> Any ideas?

Try:

=SUMPRODUCT((H8:S8>=V5)*(H8:S8<=V6),H11:S11)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Bruce" <Bruce@discussions.microsoft.com> wrote in message
news:B64BA953-C5DD-42A0-BF9C-D817BF73075D@microsoft.com...
> I want to get the sum between a range of dates.
>
> H8:S8 contain the dates
> H11:S11 contains the values
> V5 is the start date
> V6 is the end date
>
> I have tried the following as an array formula but if only works if V5 and
> V6 are at least that of range H8:S8
>
> =SUM(IF(AND(H8:S8>=V5,H8:S8<=V6),H11:S11))
>
> In other words I want it to sum the cell the do meet the criteria.
>
> Any ideas?

Close. Try:

=SUM(IF((H8:S8>=V5)*(H8:S8<=V6),H11:S11))

Array-entered.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I want to get the sum between a range of dates.
>
>H8:S8 contain the dates
>H11:S11 contains the values
>V5 is the start date
>V6 is the end date
>
>I have tried the following as an array formula but if
only works if V5 and
>V6 are at least that of range H8:S8
>
>=SUM(IF(AND(H8:S8>=V5,H8:S8<=V6),H11:S11))
>
>In other words I want it to sum the cell the do meet the
criteria.
>
>Any ideas?
>.
>