Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-22-2017, 06:22 AM
SLCREW SLCREW is offline Using SUMIF when criteria is a formula Windows 10 Using SUMIF when criteria is a formula Office 2013
Novice
Using SUMIF when criteria is a formula
 
Join Date: Feb 2017
Posts: 3
SLCREW is on a distinguished road
Default Using SUMIF when criteria is a formula

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.
Reply With Quote
  #2  
Old 02-22-2017, 07:04 AM
xor xor is offline Using SUMIF when criteria is a formula Windows 10 Using SUMIF when criteria is a formula Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

You can try:

=SUMPRODUCT(--(J5:J337<M5:M337),M5:M337)
Reply With Quote
  #3  
Old 02-22-2017, 07:38 AM
SLCREW SLCREW is offline Using SUMIF when criteria is a formula Windows 10 Using SUMIF when criteria is a formula Office 2013
Novice
Using SUMIF when criteria is a formula
 
Join Date: Feb 2017
Posts: 3
SLCREW is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 02-22-2017, 07:54 AM
xor xor is offline Using SUMIF when criteria is a formula Windows 10 Using SUMIF when criteria is a formula Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

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.
Reply With Quote
  #5  
Old 02-22-2017, 08:23 AM
SLCREW SLCREW is offline Using SUMIF when criteria is a formula Windows 10 Using SUMIF when criteria is a formula Office 2013
Novice
Using SUMIF when criteria is a formula
 
Join Date: Feb 2017
Posts: 3
SLCREW is on a distinguished road
Default

Awesome! It did work. Thanks for the help.
Reply With Quote
  #6  
Old 02-22-2017, 10:21 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Using SUMIF when criteria is a formula Windows 7 64bit Using SUMIF when criteria is a formula Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

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
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Using SUMIF when criteria is a formula formula, 2 criteria, one result cjdstephenson Excel 4 06-02-2015 09:17 PM
Using SUMIF when criteria is a formula Formula with 3 Criteria bremen22 Excel 3 09-24-2013 11:39 AM
Using SUMIF when criteria is a formula SUMIF Formula question jcaswell Excel 3 05-22-2011 02:52 AM
Using SUMIF when criteria is a formula Need Help With SUMIF Formula In R1C1 Style Todd Excel 9 02-27-2010 08:30 PM
Using SUMIF when criteria is a formula Multiple criteria in SUMIF? pumpkin head Excel 1 02-17-2006 09:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:19 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft