+ Reply to Thread
Results 1 to 3 of 3

A formula to return values for financial statements isn't working

  1. #1
    Registered User
    Join Date
    11-27-2020
    Location
    The Hague, the Netherlands
    MS-Off Ver
    newest
    Posts
    2

    A formula to return values for financial statements isn't working

    (I'm Dutch, so sorry for spelling mistakes and/or unclear statement about the code)

    I want to put together a financial oversight or balance sheet for myself so that I can keep my income and spendings in check. I found a formula in an already existing spreadsheet made by a business, but they only had a spreadsheet on a yearly basis, whilst I want to have a spreadsheet seperated into different tabs(every tab being a different month) with my day to day income and spendings.

    The original formula is:

    =-IF(ISBLANK($F14);;SUM.IFS(Transacties!$E:$E;Transacties!$B:$B;$F14; ARRAYFORMULA(MONTH(Transacties!$C:$C));MONTH(H$4)))

    Transacties!$E:$E refering to the column of amount of the transactions in euro's in the transactions tab.

    Transacties!$B:$B refering to the column of types of transactions (e.g. income, repairs, travel costs).

    $F14 in the balance tab refering to the type of transaction to compare if it matches up with the transactions tab.

    Transactions!$C:$C is the date given in the transactions sheet

    and probably kinda obviously H$4 in the balance tab is to compare it to the transactions tab.

    My formula is as follows:

    =-IF(ISBLANK(K$2);;SUM.IFS(Transacties!$D:$D;Transacties!$C:$C;K$2;ARRAYFORMULA(DATE(Transacties!$E:$E));DATE($K4)))

    In my sheet Transacties!$D:$D is the amount.

    Transacties!$C:$C the type of transaction.

    K$2 the comparator.

    Transacties!$E:$E the date.

    $K4 the comparator for the date.

    But upon trying out my formula it's telling me "Matrixarguments for SUM.IFS have different sizes"

    Thanks in advance, I've been trying to figure it out on my own, but I can't for the life of me.

    I've posted this on another forum and r/spreadsheets as well and I got referred to this site. I know it's spreadsheets and not Excel, but I was hoping someone could help me either way
    Attached Files Attached Files
    Last edited by Flame_Bird; 11-29-2020 at 12:31 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,390

    Re: A formula to return values for financial statements isn't working

    Flame_Bird

    Attached reworks your Jan and Feb sheets to show Income and Expenditure for each month.

    Cols B - D show the Income, Cols E - O your outgoings in thier different Categories.
    If you have multiple costs or incomes on the same day, just add extra rows with the same dates. It does not matter how many rows you use for different months.
    Opening Balance each month is linked to the Closing Balance of the month before. (Jan has a dummy Opening value of 5000).

    Rows 41 - 48 show what you earned and spent in each Category in the month, and whether you over spent or saved anything.

    Hope this helps.

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 11-27-2020 at 10:29 PM.

  3. #3
    Registered User
    Join Date
    11-27-2020
    Location
    The Hague, the Netherlands
    MS-Off Ver
    newest
    Posts
    2

    Re: A formula to return values for financial statements isn't working

    Thanks for helping me out! My problem's been solved. I really appreciate you taking your time to help me out, have a wonderful day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Why is my index match formula not working to return the values I expect from a list
    By carrollm in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 04-12-2019, 02:23 AM
  2. [SOLVED] formula to return multiple values in single cell not working
    By judiejean in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-11-2017, 11:45 AM
  3. How to unpivot financial statements
    By stephme55 in forum Excel General
    Replies: 1
    Last Post: 10-02-2016, 08:48 AM
  4. Replies: 28
    Last Post: 07-17-2015, 07:32 AM
  5. Replies: 5
    Last Post: 02-03-2014, 12:16 PM
  6. Financial Options IF Statements
    By baspy6 in forum Excel General
    Replies: 0
    Last Post: 09-28-2011, 12:12 PM
  7. Financial Statements
    By Xx7 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-16-2011, 09:49 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1