Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a spreadsheet of my bank account transaction history in this format:

  | A          | B        | C        | D
1 | Date       | Amount   | Balance  | Category
2 | 2013-03-10 | $ -10.59 | $3700.76 | Food shopping
3 | 2013-03-11 | $ - 5.10 | $3695.66 | Car parking
4 | 2013-03-11 | $ 100.00 | $4695.66 | Salary

In my spreadsheet the Balance column is hidden because I recalculate it myself.

I want to have a summary for each month with data looking like this:

  | A       | B        | C           | D
1 | Month   | Income   | Expenditure | Net
2 | 2013-03 | $ 100.00 |     $ 15.69 | $ 84.31
3 | 2013-04 | $  00.00 |     $ 00.00 | $ 00.00

These are the formulas I have right now, so I can compute Net fine, but I don't know how to compute Income or Expenditure on a monthly basis because SUMIF only lets me do criteria on a single column (in this case, the Date column, but I want to only sum rows >0.0 for Income and <0.0 for Expenditure):

Net = =SUMIF( MonthAndYear( Txn!$A:$A ) , $A$2, Txn!$B:$B )
share|improve this question

2 Answers 2

up vote 1 down vote accepted

You can use SUMIFS to sum with multiple conditions. For example:

=SUMIFS(B1:B6,A1:A6,"2013-03",B1:B6,">0")

This assumes your month column is a string and not a date. Because SUMIF only allows direct comparisons and cannot run a function on the month column. So it could not convert a date to a number for example.

share|improve this answer
    
Thanks. I added a new column to Txn which was the year and month E. This is what I ended-up using: =SUMIFS( Txn!B:B, Txn!E:E, A2, Txn!B:B, "<0"). –  Dai Apr 1 '13 at 0:57

In Excel 2007 or later you can use SUMIFS for multiple conditions so for Income in a specific month try

=SUMIFS(Txn!B:B,Txn!B:B,">0",Txn!A:A,">="&A2,Txn!A:A,"<"&EOMONTH(A2,0)+1)

where A2 contains the first of the month (however formatted)

and for expenditure that can be similar, i.e.

=SUMIFS(Txn!B:B,Txn!B:B,"<0",Txn!A:A,">="&A2,Txn!A:A,"<"&EOMONTH(A2,0)+1)*-1

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.