Excel - Sum values if their row meets more than one criteria

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 )
``````
-

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.

-
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`

-