![]() |
#1
|
|||
|
|||
![]()
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.
|
#2
|
|||
|
|||
![]()
You can try:
=SUMPRODUCT(--(J5:J337<M5:M337),M5:M337) |
#3
|
|||
|
|||
![]()
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.
|
#4
|
|||
|
|||
![]()
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. |
#5
|
|||
|
|||
![]()
Awesome! It did work. Thanks for the help.
|
#6
|
||||
|
||||
![]()
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 |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
cjdstephenson | Excel | 4 | 06-02-2015 09:17 PM |
![]() |
bremen22 | Excel | 3 | 09-24-2013 11:39 AM |
![]() |
jcaswell | Excel | 3 | 05-22-2011 02:52 AM |
![]() |
Todd | Excel | 9 | 02-27-2010 08:30 PM |
![]() |
pumpkin head | Excel | 1 | 02-17-2006 09:06 AM |