Forum Discussion
Using Sumif with a table
Using a table with two Columns: Transaction date(M/D/Y) and amount, sorted by Transaction Date.
I need to report on the total amount for each month.
Month Month Total
1 $xxxxxx
2 $xxxxxx
3
I am trying to use Sumif for each month
=Sumif(range, criteria, [range_sum])
The values are in a table. So when I put the range in I start at the top and select to the bottom of the column. That gives me "Table8[Transaction Date]". Then comes the problem. The criteria is the month of the transaction matches the Month row value (Report Total for Month 3). (Month(Table8[Transaction Date]= $J8. The range_sum is "Table8[Amount($)]".
I come up with =sumif(Table8[Transaction Date],(Month(Table8[Transaction Date] = $J8)),Table8[Amount($)]))
Excel gives me a generic formula error.
Can anyone see where I am going wrong??
TIA
4 Replies
Hi GregDC
The issue has nothing to do with the fact that you use a Table instead of a Range. In a nutshell what you would need to do is:
=SUMIF(MONTH(Table8[Transaction Date]), J8, Table8[Amount($)])but that's not possible as MONTH(Table8[Transaction Date]) returns an Array where SUMIF expects a Range
Alternatives exist (depends on the Excel version you run). Assuming you won't want to add a calculated [Month] column to your table, below is one with a PivotTable (sample attached):
- Drop the [Transaction Date] field to the Rows area. Excel will auto. group dates by Days, Months...
- Remove all but Months (Transaction Date)
- Drop the [Amount ($)] field to the Values area
- Ok, I have done Pivot tables in the past (many years ago!) So I will dust off those brain cells and see what I can do. Thanks
- Would you mind uploading the file here without sensitive data.
- Let me work on that today. Thanks
