Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF & MONTH() ?
Hi,
I have a spreadsheet that contains a list of dates in A column, text in B column, and amounts in C column (simple bookkeeping). On a different sheet in the workbook, I want to collect the totals by month (for budgeting). How can I setup a formula that will SUMIF by MONTH ? I just want it to scan the whole list and total the amounts for a given month but I can't 'seem to figure it out. Thank you, D |
#2
|
|||
|
|||
Hey D -
You can use an 'array formula' for the calculation - here's a sample: =SUM(IF(Sheet1!$A$4:$A$14=Sheet2!B3,Sheet1!$C$4:$C $14)) the trick to making this work is that after you compose the formula, you MUST hold down your 'shift' and 'ctrl' key before pressing enter. Otherwise you'll error out. Also, col. A of your source data must be Jan, Feb, Mar, etc. and a similar table must be created on the sheet you want the data to sum to. If you have dates like 01/5/05, or January 15, 2005 - make sure that you had a col. adjacent where you can denote that data as just January, etc. -- tenaj "thornomad" wrote: Hi, I have a spreadsheet that contains a list of dates in A column, text in B column, and amounts in C column (simple bookkeeping). On a different sheet in the workbook, I want to collect the totals by month (for budgeting). How can I setup a formula that will SUMIF by MONTH ? I just want it to scan the whole list and total the amounts for a given month but I can't 'seem to figure it out. Thank you, D |
#3
|
|||
|
|||
You may want to spend a little time learning about data|pivottables.
If you select your range (include one row of headers) then data|pivottable follow the wizard until you see a dialog with a Layout button on it. click that button drag the date "button" to the row field drag the amount button to the data field finish up that wizard. Now you have individual dates in column A. rightclick and group those dates by month and year. If you can't group that data, then you probably have non-date stuff in your raw data (blanks or text). Fix it and try again. thornomad wrote: Hi, I have a spreadsheet that contains a list of dates in A column, text in B column, and amounts in C column (simple bookkeeping). On a different sheet in the workbook, I want to collect the totals by month (for budgeting). How can I setup a formula that will SUMIF by MONTH ? I just want it to scan the whole list and total the amounts for a given month but I can't 'seem to figure it out. Thank you, D -- Dave Peterson |
#4
|
|||
|
|||
I had never seen the pivot table thing before ... I have been playing with
that. Very cool. Seems like a reasonable way to organize everything out of one enormous list. Awesome. thank you! "Dave Peterson" wrote: You may want to spend a little time learning about data|pivottables. If you select your range (include one row of headers) then data|pivottable follow the wizard until you see a dialog with a Layout button on it. click that button drag the date "button" to the row field drag the amount button to the data field finish up that wizard. Now you have individual dates in column A. rightclick and group those dates by month and year. If you can't group that data, then you probably have non-date stuff in your raw data (blanks or text). Fix it and try again. thornomad wrote: Hi, I have a spreadsheet that contains a list of dates in A column, text in B column, and amounts in C column (simple bookkeeping). On a different sheet in the workbook, I want to collect the totals by month (for budgeting). How can I setup a formula that will SUMIF by MONTH ? I just want it to scan the whole list and total the amounts for a given month but I can't 'seem to figure it out. Thank you, D -- Dave Peterson |
#5
|
|||
|
|||
Now you've done it....<vbg:
Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx thornomad wrote: I had never seen the pivot table thing before ... I have been playing with that. Very cool. Seems like a reasonable way to organize everything out of one enormous list. Awesome. thank you! "Dave Peterson" wrote: You may want to spend a little time learning about data|pivottables. If you select your range (include one row of headers) then data|pivottable follow the wizard until you see a dialog with a Layout button on it. click that button drag the date "button" to the row field drag the amount button to the data field finish up that wizard. Now you have individual dates in column A. rightclick and group those dates by month and year. If you can't group that data, then you probably have non-date stuff in your raw data (blanks or text). Fix it and try again. thornomad wrote: Hi, I have a spreadsheet that contains a list of dates in A column, text in B column, and amounts in C column (simple bookkeeping). On a different sheet in the workbook, I want to collect the totals by month (for budgeting). How can I setup a formula that will SUMIF by MONTH ? I just want it to scan the whole list and total the amounts for a given month but I can't 'seem to figure it out. Thank you, D -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
sumif date is in certain month | Excel Worksheet Functions | |||
Add one month to the previuos month | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions | |||
How to extract month number from month name | Excel Discussion (Misc queries) |