How can I add up all values that fall between 2 date ranges. My data
is sorted like a database, so Col A contains dates and Col B sales for
each of the dates in Col A.

So if I want to sum between 01/01/07 and 31/01/07 which would be
Sum(B2:B33) how could I do that, without having to 'manually' sum for
each month?

Thanks

=SUMIF(A2:A100,"1/1/07",B2:B100)-SUMIF(A2:A100,"31/01/07",B2:B100)

*Untested. Not sure if date format is okay, might require some tinkering.
--
Best Regards,

Luke M


"Sean" wrote:

How can I add up all values that fall between 2 date ranges. My data
is sorted like a database, so Col A contains dates and Col B sales for
each of the dates in Col A.

So if I want to sum between 01/01/07 and 31/01/07 which would be
Sum(B2:B33) how could I do that, without having to 'manually' sum for
each month?

Thanks

Some tinkering could mean something like:

=SUMIF(A2:A100,"="&date(2007,1,1),B2:B100)
-SUMIF(A2:A100,"="&date(2007,2,1),B2:B100)

(I included both Jan 1, 2007 and Jan 31, 2007.

Another one:
=sumproduct(--(text(a2:a100,"yyyymm")="200701")

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Luke M wrote:

=SUMIF(A2:A100,"1/1/07",B2:B100)-SUMIF(A2:A100,"31/01/07",B2:B100)

*Untested. Not sure if date format is okay, might require some tinkering.
--
Best Regards,

Luke M

"Sean" wrote:

How can I add up all values that fall between 2 date ranges. My data
is sorted like a database, so Col A contains dates and Col B sales for
each of the dates in Col A.

So if I want to sum between 01/01/07 and 31/01/07 which would be
Sum(B2:B33) how could I do that, without having to 'manually' sum for
each month?

Thanks


--

Dave Peterson

Missed a closing paren:

=sumproduct(--(text(a2:a100,"yyyymm")="200701"))

Dave Peterson wrote:

Some tinkering could mean something like:

=SUMIF(A2:A100,"="&date(2007,1,1),B2:B100)
-SUMIF(A2:A100,"="&date(2007,2,1),B2:B100)

(I included both Jan 1, 2007 and Jan 31, 2007.

Another one:
=sumproduct(--(text(a2:a100,"yyyymm")="200701")

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Luke M wrote:

=SUMIF(A2:A100,"1/1/07",B2:B100)-SUMIF(A2:A100,"31/01/07",B2:B100)

*Untested. Not sure if date format is okay, might require some tinkering.
--
Best Regards,

Luke M

"Sean" wrote:

How can I add up all values that fall between 2 date ranges. My data
is sorted like a database, so Col A contains dates and Col B sales for
each of the dates in Col A.

So if I want to sum between 01/01/07 and 31/01/07 which would be
Sum(B2:B33) how could I do that, without having to 'manually' sum for
each month?

Thanks


--

Dave Peterson

--

Dave Peterson