Question posted 2014 · +4 upvotes
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
Accepted answer +7 upvotes
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")
2 code variants in this answer
- Variant 1 — 1 lines, starts with
=SUMPRODUCT((YEAR(G6:G11)=2013)*H6:H11) - Variant 2 — 1 lines, starts with
=SUMIFS(H6:H11,G6:G11,">=01-Jan-2013",G6:G11,"<=31-Dec-2013…
Top excel Q&A (6)
- Shortcut to Apply a Formula to an Entire Column in Excel +335 (2011)
- How should I escape commas and speech marks in CSV files so they work in Excel? +136 (2012)
- Convert xlsx to csv in linux command line +96 (2012)
- How to create a link inside a cell using EPPlus +50 (2011)
- IF statement: how to leave cell blank if condition is false ("" does not work) +44 (2013)
- T-SQL: Export to new Excel file +44 (2012)
excel solutions on this site
.