SUMIF by category and date in range

I have a table where I keep a transaction log of expenditures as so (excerpt)

Date Paid Purpose

where the date is the date when the transaction is conducted, Paid is the actual amount paid and Purpose is an item from a column in a table in the same worksheet.

The log runs across different months and different years as well, what I am trying to achieve on a different page is a total by month for specific category in a particular year like so:

 May-2012 Jun-2012 Jul-2012 etc
Purpose 1
Purpose 2
Purpose 3

I am more confused by what I'm seeing online and have not yet edged to an answer. All help is greatly appreciated.

2 Answers

If this is what you want,

set B13 to

=SUMIFS($B$2:$B$10, $A$2:$A$10, ">="&B$12, $A$2:$A$10, "<"&(EOMONTH(B$12,0)+1), $C$2:$C$10, "="&$A13)

and drag/fill.

_Edit:

General explanation of SUMIFS (excerpted) from Excel’s online help:

Description

Adds the cells in a range that meet multiple criteria.  For example, if you want to sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10, you can use the following formula:

    =SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10")

Syntax

  SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Specific explanation of my answer:

• $B$2:$B$10 –– is the data you want to sum
• $A$2:$A$10, ">="&B$12 –– where the date (from Column A in the first part of the sheet) is on or after the first of the month in the heading of this column (B) in the second part of the sheet, and
• $A$2:$A$10, "<"&(EOMONTH(B$12,0)+1) –– the date is before (less than) the first day of the following month (i.e., the last day (“end of”) this month, plus one), and
• $C$2:$C$10, "="&$A13 –– the purpose (from Column C in the first part of the sheet) is the same as the purpose in the first column (A) in this row.

1. Convert your data into an Excel Table (Insert>Table, check the header box)
2. Select a cell in your new Table and create a Pivot Table (Insert>Pivot Table)
3. Organize the Pivot Table:
   • Column Labels = Date (can be grouped at whatever level you like, e.g. month)
   • Row Labels = Purpose
   • Values = Paid (formated to your local currency)