# sumif and summary income outcome per months

1. ## 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

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

3. ## 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. ## Re: sumif and summary income outcome per months

Glenn, you are a star!!!

5. ## Re: sumif and summary income outcome per months

thanks Caracalla as well!

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