Excel Sum If Year Equals
excel, excel-formula, if-statement, sum
Solution
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")
Problem
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