Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Year End totals
Hi,
I'm trying to compile year end totals across 24 worksheets into one sheet for last year. Each worksheet has a category column that describes individual entries. DATE NUM DESC CATEGORY DEBIT(-) CREDIT(+) BALANCE 4 - 12/15/12 SAL Sales SalesMN 176.18 1176.18 5 - 12/17/12 DCD Quickbooks Accounting 149.99 1026.19 6 - 12/18/12 ATF Inventory Inventory 324.07 702.12 Here's the formula I've tried but it won't total the year across the sheets. Works great on just one sheet. Without YEAR. =SUMIF(YEAR(D4:D100,"=Accounting",E3:E100)) Any help would be appreciated. Thank you, thank you, thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Year End totals
Pamela Rease presented the following explanation :
Hi, I'm trying to compile year end totals across 24 worksheets into one sheet for last year. Each worksheet has a category column that describes individual entries. DATE NUM DESC CATEGORY DEBIT(-) CREDIT(+) BALANCE 4 - 12/15/12 SAL Sales SalesMN 176.18 1176.18 5 - 12/17/12 DCD Quickbooks Accounting 149.99 1026.19 6 - 12/18/12 ATF Inventory Inventory 324.07 702.12 Here's the formula I've tried but it won't total the year across the sheets. Works great on just one sheet. Without YEAR. =SUMIF(YEAR(D4:D100,"=Accounting",E3:E100)) Any help would be appreciated. Thank you, thank you, thank you. Are there separate sheets for each category? If so then IMO it's a bad design! There's no reason why you shouldn't use a single sheet for all categories plus a summary sheet. The summary sheet would collect from each category, and could be set up to show by month/QTR/YTD. On the other hand.., I see the name "Quickbooks" in your list and so begs me to ask why you don't use that app to generate a custom report for the fiscal/calendar year?<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Year End totals
GS wrote:
Pamela Rease presented the following explanation : Hi, I'm trying to compile year end totals across 24 worksheets into one sheet for last year. Each worksheet has a category column that describes individual entries. DATE NUM DESC CATEGORY DEBIT(-) CREDIT(+) BALANCE 4 - 12/15/12 SAL Sales SalesMN 176.18 1176.18 5 - 12/17/12 DCD Quickbooks Accounting 149.99 1026.19 6 - 12/18/12 ATF Inventory Inventory 324.07 702.12 Here's the formula I've tried but it won't total the year across the sheets. Works great on just one sheet. Without YEAR. =SUMIF(YEAR(D4:D100,"=Accounting",E3:E100)) Any help would be appreciated. Thank you, thank you, thank you. Are there separate sheets for each category? If so then IMO it's a bad design! There's no reason why you shouldn't use a single sheet for all categories plus a summary sheet. The summary sheet would collect from each category, and could be set up to show by month/QTR/YTD. On the other hand.., I see the name "Quickbooks" in your list and so begs me to ask why you don't use that app to generate a custom report for the fiscal/calendar year?<g Thanks for your reply. Bad design is right. But I inherited it. No. There are separate sheets for each month with categories dispersed randomly on each monthly sheet. What I'm trying to do is create a yearly summary sheet with individual category totals for the year. And Quickbooks was just a demonstrative entry. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Year End totals
Pamela Rease explained :
No. There are separate sheets for each month with categories dispersed randomly on each monthly sheet. What I'm trying to do is create a yearly summary sheet with individual category totals for the year. And Quickbooks was just a demonstrative entry. I don't know if this will help but I'll give it a shot... I have a Excel-based app called "Invoicing: Simple Bookkeeping" which I designed for people who were self-employed or ran home-based enterprises, but didn't want to invest the time or $$ in a commercial accounting software. This app is designed to record transactions for a single calendar year (ie: personal tax period). It uses 3 sheets to record expenses, income, and P&L summary. All 3 sheets impliment month cols that pick up their respective amounts based on the month of transaction dates. The expense/income sheets have an input area to the left of the month cols where users list their transaction details. Both sheets use categories and subcategories via DV dropdowns so the P&L sheet can collect amounts appropriate to there distribution. The P&L is read only, as are the month cols on Expenses/Income. These 2 sheets total months only while P&L totals months, quarters, and YTD. I could have added the latter 2 to Expense/Income but didn't because they have other features that would make the sheet 'too busy' for my liking. Also, P&L has analysis features that work appropriate to its layout. The point is that it's much more efficient and easier to manage if the whole year was on 1 sheet, with month cols for fiscal period distribution. Even though you inherited this project, is there any reason why you can't improve on it so it's more efficient and better able to improve user productivity? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Year End totals
GS wrote:
Pamela Rease explained : No. There are separate sheets for each month with categories dispersed randomly on each monthly sheet. What I'm trying to do is create a yearly summary sheet with individual category totals for the year. And Quickbooks was just a demonstrative entry. I don't know if this will help but I'll give it a shot... I have a Excel-based app called "Invoicing: Simple Bookkeeping" which I designed for people who were self-employed or ran home-based enterprises, but didn't want to invest the time or $$ in a commercial accounting software. This app is designed to record transactions for a single calendar year (ie: personal tax period). It uses 3 sheets to record expenses, income, and P&L summary. All 3 sheets impliment month cols that pick up their respective amounts based on the month of transaction dates. The expense/income sheets have an input area to the left of the month cols where users list their transaction details. Both sheets use categories and subcategories via DV dropdowns so the P&L sheet can collect amounts appropriate to there distribution. The P&L is read only, as are the month cols on Expenses/Income. These 2 sheets total months only while P&L totals months, quarters, and YTD. I could have added the latter 2 to Expense/Income but didn't because they have other features that would make the sheet 'too busy' for my liking. Also, P&L has analysis features that work appropriate to its layout. The point is that it's much more efficient and easier to manage if the whole year was on 1 sheet, with month cols for fiscal period distribution. Even though you inherited this project, is there any reason why you can't improve on it so it's more efficient and better able to improve user productivity? I wish the whole year was on one sheet but its not. As there are hundreds of individual rows of categorized entries it would take a year to transfer the monthly data to one sheet. Do you know of any macros that might do the trick? And thank you again for all your help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Year End totals
Well.., you could send me your file and I'll load the data into my app,
copy the necessary sheets to a new book and send it back so you have a template to work from/with going forward. (You won't need/use all the other sheets included in the app's workbook) gesansomATnetscapeDOTnet (replace the ucase with appropriate symbols) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
|
|||
|
|||
Hi Pamela
I do not quite follow your formula! But FYI SUMIF/SUMIFS will not work across sheets (well not on their own) and nor will SUMIF/SUMIFS accept arrays, but there are a work arounds. I am assuming you are using Excel 2007 First you need to to put a list of your sheets in say Z1:Z24, ensure you spell the sheet names correctly, then define a name for the list, say for example "Sheets". If cells D4:D100 contain dates and you need to Sum for the year 2012. =SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!E4:E100" ),INDIRECT("'"&Sheets&"'!D4:D100"),"=01/01/2012",INDIRECT("'"&Sheets&"'!D4:D100"),"<=31/12/2012")) Sorry will have to post the other part seperately due to "Smilies"!!!! Administrator/Moderator. If you read this, can you disable the "Smilies" |
#8
|
|||
|
|||
Hi Pamela
Part 2! If D4:D100 is a list and you need to extract the totals for "Accounting" then use the SUMIF within the SUMPRODUCT. =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!D4:D100") ,"Accounting",INDIRECT("'"&Sheets&"'!E4:E100")) ) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Year End totals
Kevin@Radstock wrote:
Hi Pamela I do not quite follow your formula! But FYI SUMIF/SUMIFS will not work across sheets (well not on their own) and nor will SUMIF/SUMIFS accept arrays, but there are a work arounds. I am assuming you are using Excel 2007 First you need to to put a list of your sheets in say Z1:Z24, ensure you spell the sheet names correctly, then define a name for the list, say for example "Sheets". If cells D4:D100 contain dates and you need to Sum for the year 2012. =SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!E4:E100" ),INDIRECT("'"&Sheets&"'!D4:D100"),"=01/01/2012",INDIRECT("'"&Sheets&"'!D4:D100"),"<=31/12/2012")) Sorry will have to post the other part seperately due to "Smilies"!!!! Administrator/Moderator. If you read this, can you disable the "Smilies" Thanks Kevin. I really appreciate your efforts. Will give it a try and get back later in the week. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum year to date totals for particular month | Excel Worksheet Functions | |||
Year to date totals | Excel Discussion (Misc queries) | |||
Matching a Year and then Summing the totals | Excel Programming | |||
Year Totals | Excel Worksheet Functions | |||
Need totals of values that fall within a given year | Excel Worksheet Functions |