Using Array Formulas for Auto Formatted Tables

[Excel Pro]

Hi,

I have an auto formatted table with dates, client names and amounts. I need to do the sum of amount by months to prepare a chart. I am using this formula but it is giving the sum of all rows instead of giving sum for only the month of January.

{=SUM(IF(MONTH(Table1[Date]=1),Table1[Amount],))}

This same formula works well with normal range of cells.
{=SUM(IF(MONTH($C$10:$C$14)=1,$D$10:$D$14,0))}

How can I calculate the total amount for each month using data from auto formatted table?

Thanks for help.

 

[NARAYANK991]

Hi ,

First , I am not sure whether your original formula in the worksheet is the same as what you have posted here , but your posted formula has probably a typo ; it should actually be :

=SUM(IF(MONTH(Table1[Date])=1,Table1[Amount],))

Narayan

 

[pecoflyer]

Why use an array formula which slows things down, rather than SUMPRODUCT or SUMIFS ?