Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    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.

    Thank you for your advice!

    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 Attached Files
    Last edited by CaptainB; 12-31-2012 at 12:03 PM.

  • #2
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •