Flash Website Builder- Trendy Site Builder is a Flash Site Building tool that helps users build stunning websites. Check Out Custom Custom Logo Design by LogoBee. Website Design and Free Logo Templates available.
 CodingForums.com Resolved Excel: Sum of income by month - formula error

Before you post, read our: Rules & Posting Guidelines

Enjoy an ad free experience by logging in. Not a member yet? Register.
12-31-2012, 10:45 AM   PM User | #1
CaptainB
Regular Coder

Join Date: Jun 2007
Posts: 805
Thanks: 123
Thanked 23 Times in 23 Posts
Excel: Sum of income by month - formula error

Hey guys,

I have a excel document that I use to do my accounting.
On the first sheet I have a table that should display the income by month. The details for this table should be pulled from another worksheet in the same document.

What I want to do is to be able to auto-detect the income for each month. This means, that I would have a list of dates and an amount for that date in the corresponding row. I then want to have excel to automaticly detect the income for each month.

I found a formula that I modified, but it does throw an error that I am not able to solve. I hope somebody would take a look and give some advice.

In the attached file:
The first table 'Omsætning' should show the total income for the corresponding month. The values should be pulled from the worksheet named 'Omsætning'. If you look at the column for 'Jan (January)', you will see the formula that does not work.

EDIT:
I think I will post the formula here too, just in case the error is easy to spot:
Code:
`=SUMPRODUCT(IF(ISBLANK(Omsætning!B8:B206);0;1);--(MONTH(Omsætning!B8:B206)=MONTH(B9));--(YEAR(Omsætning!B8:B206)=YEAR(B9));Omsætning!E8:E206)`
Kind regards,
Attached Files
 Book1.zip (13.9 KB, 36 views)

Last edited by CaptainB; 12-31-2012 at 01:03 PM..

 12-31-2012, 01:02 PM PM User | #2 CaptainB Regular Coder   Join Date: Jun 2007 Posts: 805 Thanks: 123 Thanked 23 Times in 23 Posts I think I solved the issue. The formula was correct, however, because it is an array formula I had to create it by hitting CTRL + SHIFT + ENTER instead of just enter. Solution found on this page: http://www.ozgrid.com/forum/showthread.php?t=68280

 Bookmarks

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off Forum Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home :: Client side development     JavaScript programming         DOM and JSON scripting         Ajax and Design         JavaScript frameworks         Post a JavaScript     HTML & CSS     XML     Flash & ActionScript         Adobe Flex     Graphics and Multimedia discussions     General web building         Site reviews         Building for mobile devices :: Server side development     Apache configuration     Perl/ CGI     PHP         Post a PHP snippet     MySQL         Other Databases     Ruby & Ruby On Rails     ASP     ASP.NET     Java and JSP     Other server side languages/ issues         ColdFusion         Python :: Computing & Sciences     Computer Programming     Computer/PC discussions     Geek News and Humour Web Projects and Services Marketplace     Web Projects         Small projects (quick fixes and changes)         Medium projects (new script, new features, etc)         Large Projects (new web application, complex features etc)         Unknown sized projects (request quote)         Vacant job positions         Looking for work/ for hire         Project collaboration/ partnership         Paid work offers and requests (Now CLOSED)     Career, job, and business ideas or advice     Domains, Sites, and Designs for sale         Domains for sale         Websites for sale         Design templates and graphics for sale :: Other forums     Member Offers     Forum feedback and announcements

All times are GMT +1. The time now is 01:32 PM.