# Using Sumif with a table

Copper Contributor

# 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

# Re: Using Sumif with a table

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

# Re: Using Sumif with a table

Let me work on that today. Thanks

# Re: Using Sumif with a table

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