# Excel formulas

Closed
ybaudier - Oct 4, 2010 at 11:54 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 5, 2010 at 06:43 AM
Hello,

I have to create a formula that will sum a column if it meets certain criteria. The criteria must sum the list if the date fall within the >12/31/2009 and > than 5000. Then I have to sum the reverse, in other words, sum if date falls < 01/01/2010 and is < than 4999.99. Can you help?

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Oct 5, 2010 at 06:43 AM
suppose sample data from 1 to B11 is like this

date amount
12/25/2009 3460
12/26/2009 2427
12/27/2009 3164
12/28/2009 1498
12/29/2009 2506
12/30/2009 5838
12/31/2009 491
1/1/2010 6589
1/2/2010 4743
1/3/2010 9254

now in any empty cell cell copy this formula

=SUMPRODUCT((A2:A11>DATEVALUE("12/31/09"))*(B2:B11>5000)*(B2:B11))

you will get 15843

you can use any combination in the formula.

modify a11 and B11 depending upon the length of the data.