I need Excel formula that is sum based on multiple criteria ranges

I am having difficulties with coming up with a correct formula. I need a formula that says to sum F12:F106 with the following two criterias.

1.  criteria range of C12:C106 and criteria 36

2. criteria range of D12:D106 criteria of date is greater than 12/31/2012 and less than 1/1/2014

I am doing fine all the way up through the first criteria. The date formula is what is giving me fits.
jimstricklandAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Which version of Excel? In Excel 2007 or later you can use SUMIFS e.g.

=SUMIFS(F12:F106,C12:C106,36,D12:D106,">"&DATE(2012,12,31),D12:D106,"<"&DATE(2014,1,1))

or in earlier versions you can use SUMPRODUCT like this

=SUMPRODUCT(F12:F106,(C12:C106=36)*(D12:D106>DATE(2012,12,31))*(D12:D106<DATE(2014,1,1)))

or given that the date range is a whole year

=SUMPRODUCT(F12:F106,(C12:C106=36)*(YEAR(D12:D106)=2013))

regards, barry
0
 
jimstricklandAuthor Commented:
This is Office 2010. Your first formula worked perfectly. Thanks.
0
 
jimstricklandAuthor Commented:
Thanks. The first formula was exactly what I needed.
0
All Courses

From novice to tech pro — start learning today.