Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating cells based on dates
I have 2 columns: one that has dates ranging from 10/03/03 through 12/31/05.
The second has money deposited on the previous columns date. I can not seem to find the right 'SUMIF' syntax to give me a total of money deposited for a certain date range. for example how much money deposited in March 2004. Any help would be greatl appreciated. - Superchikn |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating cells based on dates
=sumproduct(--(text(a1:a10,"yyyymm")="200403"),(b1:b10))
Adjust the ranges to match--but you can't use whole columns. =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 into using data|pivottable. You can group by the date field and not have to use separate formulas for each month/year. Superchikn wrote: I have 2 columns: one that has dates ranging from 10/03/03 through 12/31/05. The second has money deposited on the previous columns date. I can not seem to find the right 'SUMIF' syntax to give me a total of money deposited for a certain date range. for example how much money deposited in March 2004. Any help would be greatl appreciated. - Superchikn -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a commission chart based on dates worked? | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Calculating Cells n% displayed in a cell? | Excel Worksheet Functions | |||
Adding colour to a range of cells based on one of the cells v... | Excel Discussion (Misc queries) | |||
Calculating Due Dates Based on Payments | Excel Worksheet Functions |