Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Dollar Formula
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date and Dollar Formula
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
formula to give text of value($1.01 = "one dollar and one cents") | Excel Worksheet Functions | |||
What is the formula for rounding a dollar amount to the nearest ni | New Users to Excel | |||
Dollar and date format | Excel Discussion (Misc queries) |