Date and Dollar Formula

[Guest]

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]

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