Excel formulas

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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?

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.