Excel SUMIFs Formula Problems

I’m trying to set up an Excel spreadsheet with a data set that includes a number with a date/time then to sum the numbers between dates and times I specify.  As an example, this simple table lists home sale prices by the date on which the sale occurred.

 

In this example, the data table is A2 to B11 where column A is date and column B is sales price.  I created the simple data analysis table in D1 to F3 where a start date is entered in D2 and a finish date is entered in D3.

The online help for the SUMIFS function defines its syntax as this:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

The Excel values for the dates 01/10/2012 and 02/10/2012 are 40918 and 40949, respectively.  If I create the formula in F2 using the numbers for the dates, like this:

=SUMIFS($B$2:$B$11,$A$2:$A$11,”>=40918″,$A$2:$A$11,”<=40949″)

the formula returns the correct value of $792,000.  If I use cell references for the dates, however, like this:

=SUMIFS($B$2:$B$11,$A$2:$A$11,”>=E2″,$A$2:$A$11,”<=E3″)

the formula returns a value of $0.

Any thoughts on what’s going wrong?  Having to hard code the dates into the formula makes this much less flexible.  This is the same whether I’m using Excel 2007 or Excel 2010, by the way.

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.

Comments

Use this formula

=SUMIF(A2:A11,”<"&E3,B2:B11)-SUMIF(A2:A11,"<"&E2,B2:B11)

It first sums every less than the larger date, then subtract everything less than the lower date. This effectively inclusively sums everything between the two dates.

Sorry, the comment form is closed at this time.