Excel amount if year is

Question

I got two data columns:

A:

12/31/2013

12/30/2013

12/29/2013

12/28/2013

12/27/2013

12/26/2012

IN:

ten

ten

ten

ten

ten

5

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

in the master answer 50

but when I press enter, 55 is displayed on the page.

Any thoughts?

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

Answer 1

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

Some workarounds:

• You enter an array into the formula. To do this, you need to press and hold Ctrl + Shift , and then press Enter .

• Use a formula that automatically treats input as an array, for example SUMPRODUCT :

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

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

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

Answer 2

I used this formula to do what you explained =SUMIF(D1:D2;"=2014";B1:B2) where D1==YEAR(A1) and `D2 = YEAR (A2) '. You can do the same for your example.