Excel Sum If Year Equals

Question

I got two columns of data;

A:

12/31/2013

12/30/2013

12/29/2013

12/28/2013

12/27/2013

12/26/2012

B:

10

10

10

10

10

5

my formula is : =SUM(IF(YEAR(G6:G11)=2013,H6:H11,0),0)

in the wizard the answer is 50

but when I hit enter, it displays 55 on the page.

Any thoughts?

SOLUTION: While writing formula, press "ctrl + shift + enter" Thank you

Answer 1

I believe that you are not entering the formula as an array formula. @Alexandru is right with his comment, in that only the first cell in the range provided is being computed, so that you have YEAR(G2)=2013, which is true, and you get the sum of the whole range H6:H11.

Some workarounds:

• You array enter the formula. This will require you to press and hold Ctrl+Shift and then press Enter.

• Use a formula that automatically considers the input as array, such as SUMPRODUCT:

  =SUMPRODUCT((YEAR(G6:G11)=2013)*H6:H11)
  

• Change your logic for this sum and use SUMIFS, by using the first and last dates of the year as boundaries:

  =SUMIFS(H6:H11,G6:G11,">=01-Jan-2013",G6:G11,"<=31-Dec-2013")