+ Reply to Thread
Results 1 to 5 of 5

sumif and summary income outcome per months

  1. #1
    Registered User
    Join Date
    12-31-2019
    Location
    london uk
    MS-Off Ver
    365
    Posts
    3

    Question sumif and summary income outcome per months

    Hello and happy end of 2019 and happy 2020 all!
    I am stuck between 'match' 'lookup' and 'sumif' and I wonder if you can help, please see the attached sheet and below the snip taken from excel 365. I have a long list of data ordered by date (below just an example), I am trying to make a table at the right where I can see a summary for each month of the income (positive values) and outcomes (negative values). I used sumif but I am not able to link it to months only (jan, feb, mar etc), I want to sum all negative entries for each month and place the result in the cell Jan expenses and all the positive entries for each month and place the result in the income jan cell. Same for the remaining months, what is the correct way to do it?

    Capture.JPG


    expenses/income Jan Feb Mar Apr
    02-Jan-19 -100 expenses -475
    05-Jan-19 -150 income 500
    07-Jan-19 -125
    15-Jan-19 -70
    25-Jan-19 -30
    30-Jan-19 500
    04-Feb-19 -20
    08-Feb-19 -50
    15-Feb-19 -200
    21-Feb-19 -175
    28-Feb-19 450
    12-Mar-19 -300
    16-Mar-19 -25
    20-Mar-19 -45
    29-Mar-19 -80
    30-Mar-19 550
    07-Apr-19 -190
    11-Apr-19 -125
    16-Apr-19 -75
    22-Apr-19 -60
    30-Apr-19 -20
    30-Apr-19 475
    Attached Files Attached Files

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: sumif and summary income outcome per months

    Change D1, E1, etc, to real dates: 01/01/2019 and format as desired. Then use:

    =SUMIFS($B:$B,$A:$A,">="&D$1,$A:$A,"<="&EOMONTH(D$1,0),$B:$B,"<0")
    for expenses, and:

    =SUMIFS($B:$B,$A:$A,">="&D$1,$A:$A,"<="&EOMONTH(D$1,0),$B:$B,">0")
    for income.
    Attached Files Attached Files
    Glenn



  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    633

    Re: sumif and summary income outcome per months

    D2=sumproduct((month($a$2:$a$100)=month(d$1&0))*($b$2:$b$100<0)*($b$2:$b$100)*($a$2:$a$100<>""))

    Copy across

    D3=sumproduct((month($a$2:$a$100)=month(d$1&0))*($b$2:$b$100>0)*($b$2:$b$100)*($a$2:$a$100<>""))

    copy across

  4. #4
    Registered User
    Join Date
    12-31-2019
    Location
    london uk
    MS-Off Ver
    365
    Posts
    3

    Re: sumif and summary income outcome per months

    Glenn, you are a star!!!

  5. #5
    Registered User
    Join Date
    12-31-2019
    Location
    london uk
    MS-Off Ver
    365
    Posts
    3

    Re: sumif and summary income outcome per months

    thanks Caracalla as well!

+ 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. Projection of an outcome a number of months forward
    By lalosan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-15-2017, 11:04 AM
  2. summing up months in income statement
    By ammartino44 in forum Excel General
    Replies: 3
    Last Post: 05-28-2015, 12:30 PM
  3. [SOLVED] Income for overlapping months to be split daily...
    By sammymalta in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-04-2013, 06:11 AM
  4. [SOLVED] [Solved] Convert from daily to monthly income summary
    By androidman in forum Excel General
    Replies: 3
    Last Post: 02-12-2013, 08:44 AM
  5. Income Statement/balance sheet/cash flow summary generator
    By xleo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-04-2012, 04:10 PM
  6. Revenue/Income Summary
    By camaro03 in forum Excel General
    Replies: 15
    Last Post: 11-26-2007, 03:28 PM
  7. Replies: 2
    Last Post: 08-19-2006, 04:00 PM

Tags for this Thread

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