Sum between Dates

[Sean]

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

 

[Luke M]

=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.

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[Dave Peterson]

Missed a closing paren:

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