I am trying to sum only the values listed in a column when the values listed in that column are greater than the values in the corresponding cell in another column. I can get the result I want by using this formula "=SUM(IF(J5<M5,M5),IF(J6<M6,M6), ... IF(J337<M337, M337))", but I don't want to type in 332 "IF" statements. I tried to get the following "SUMIF" statement to work "=SUMIF(M5:M337, J5:J337<M5:M337, M5:M337)", but I'm not getting the correct result.

You can try:

=SUMPRODUCT(--(J5:J337<M5:M337),M5:M337)

When I use the "SUMPRODUCT" statement, it multiplies the results. I'm just trying to get them to add. I also tried to use just a "SUM" statement similar to your answer instead of the "SUMPRODUCT" statement, but it didn't evaluate the sum of values in the M column based on the J5:J337<M5:M337 criteria.

It multiplies an array of TRUE/FALSE values with the values in column M.

The formula I provided tests if J5<F5, tests if J6<M6 etc. up to J337<M337 and then sums the values in M5:M337 where the logical test in the same row returns TRUE.

Another possibility is to use the array formula:

SUM(IF(J5:J337<M5:M337,M5:M337))

which must be committed by holding down Ctrl and Shift before pressing Enter.

Awesome! It did work. Thanks for the help.

More about SUMPRODUCT http://www.xldynamic.com/source/xld.SUMPRODUCT.html

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post