=sumproduct(--(text(a2:a99,"yyyymm")="200701"),b2:b99)
would sum the values in B2:B99 if a2:a99 contained a date in January of 2007.
or
=sumproduct(--(text(a2:a99,"yyyymm")=text(x99,"yyyymm")),b2:b99)
(if x99 contained a date in January of 2007)
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
========
You may want to look at Data|Pivottables. You can make nice summaries and group
by month and year.
or
Add a column like:
=text(a2,"yyyymm")
(a2 contains the date)
and drag this down your range
Then sort your data by this column and use Data|Subtotals.
tsgprez wrote:
I have a spreadsheet that has a series of dropdown cells that allow users to
select month, percentage of income and type of sale. I am looking for a
formula that will allow me to create a summary based upon dollar amounts
input in a specific month. Anyone know if this is doable?
--
Dave Peterson