Year End totals

[Pamela Rease]

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(D4100,"=Accounting",E3:E100))

Any help would be appreciated.

Thank you, thank you, thank you.

 

[GS]

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(D4100,"=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

 

[Pamela Rease]

Pamela Rease presented the following explanation :

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.

 

[GS]

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

 

[Pamela Rease]

Pamela Rease explained :

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.

 

[GS]

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

 

[Kevin@Radstock]

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.
am assuming you are using Excel 2007>
First you need to to put a list of your sheets in say Z1:Z24, ensure yo
spell the sheet names correctly, then define a name for the list, sa
for example "Sheets".
If cells D4100 contain dates and you need to Sum for the year 2012.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!E4:E100"),INDIRECT("'"&Sheets&"'!D4100"),">=01/01/2012",INDIRECT("'"&Sheets&"'!D4100"),"<=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

 

[Pamela Rease]

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 D4100 contain dates and you need to Sum for the year 2012.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!E4:E100"),INDIRECT("'"&Sheets&"'!D4100"),">=01/01/2012",INDIRECT("'"&Sheets&"'!D4100"),"<=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.