Posted to microsoft.public.excel.worksheet.functions
|
|
Sum between Dates
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
|