CODEWITHSUNDEEP
excelif-statementsumexcel-formula
Nick Fleetwood
Nick Fleetwood

Reputation: 531

Excel Sum If Year Equals

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

Upvotes: 10

Views: 53148

Answers (2)

Alexandru Cimpanu
Alexandru Cimpanu

Reputation: 1069

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

Upvotes: 0

Jerry
Jerry

Reputation: 71598

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")

Upvotes: 12

Related Questions