Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-16-2017, 03:58 PM
Lluewhyn Lluewhyn is offline SUMIF with Or Windows 7 32bit SUMIF with Or Office 2016
Novice
SUMIF with Or
 
Join Date: Apr 2016
Posts: 10
Lluewhyn is on a distinguished road
Default SUMIF with Or


Googling around, it looks like popular solutions with using multiple possible criteria for SUMIF involve using SUMPRODUCT or adding multiple SUMIF functions, but this won't work for what I'm needing.

Without getting into messy details, I'm trying to use a formula that determines if something is "Off Spec". There are multiple categories why something is off spec, and a given record could be off spec for more than one reason. So, trying to use the solutions above would simply count one record twice.

One solution to the issue is having an extra (hidden) column after each entry that does something like =IF(OR(D1 ="Corrosive", E1 >70), "Off", "On"), and then doing a SUMIF off this hidden column. However, I was wondering if there was a way to do the same search from the SUMIF area instead of having to add an extra column of information.
Reply With Quote
  #2  
Old 02-16-2017, 04:17 PM
jeffreybrown jeffreybrown is offline SUMIF with Or Windows Vista SUMIF with Or Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Sometimes the messy details help.

Without any details, does this example help...

=SUM(SUMIF(A2:A13,{"Jan","Feb","Mar"},B2:B13))
Reply With Quote
  #3  
Old 02-17-2017, 07:54 AM
Lluewhyn Lluewhyn is offline SUMIF with Or Windows 7 32bit SUMIF with Or Office 2016
Novice
SUMIF with Or
 
Join Date: Apr 2016
Posts: 10
Lluewhyn is on a distinguished road
Default

Unfortunately, I don't think that will help me. That's the problem I'm complaining about. Your solution, like the others I've seen, is trying to calculate the sum when there are multiple alternatives to the same criteria. I'm trying to sum when there are singular responses to alternative criteria.

Confused?

One example I saw was using formulas to SUMIF when calculating when "Sales Area" = "West" OR "North".

What I'm wanting to do is calculate the results when "Sales Area" = "West" OR "Salesman" = "Bob". All the solutions I've seen so far would unfortunately double-count the results when both criteria were met (i.e. Bob made a sale and it was in the West area).

The workaround I've been using is to add an extra column(let's call it column Q) in the data-set that says something to the effect of =IF(OR("Sales Area" ="West", "Salesmen" = "Bob"), "West Equivalent", "Not West Equivalent"), and then doing a SUMIF on column Q where the criteria for the formula is "West Equivalent".

Another example is say I was searching for a house and deal-breakers were anything over $300,000 or anything less than 4 bedrooms. (Maybe I would then calculate "acceptable" results by using Total Homes - "Deal-Breakers"). I would want a formula that would only sum or count the values for either condition, not count houses that were over $300k and had less than 4 bedrooms twice.

Does that make sense?
Reply With Quote
  #4  
Old 02-17-2017, 08:08 AM
Lluewhyn Lluewhyn is offline SUMIF with Or Windows 7 32bit SUMIF with Or Office 2016
Novice
SUMIF with Or
 
Join Date: Apr 2016
Posts: 10
Lluewhyn is on a distinguished road
Default

I guess the quicker and easier way to explain it is asking for the OR equivalent of SUMIFS. SUMIFS let's me total values when criteria A is met AND Criteria B is met, why can't I do something equivalent for when A is met OR B is met?
Reply With Quote
  #5  
Old 02-17-2017, 08:16 AM
jeffreybrown jeffreybrown is offline SUMIF with Or Windows Vista SUMIF with Or Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Well, it slightly makes sense, but it sounds like a Sumifs would work. Instead of endless words and examples with no results, please attach an example workbook with what you have and the result you expect.
Reply With Quote
  #6  
Old 02-17-2017, 09:31 AM
Lluewhyn Lluewhyn is offline SUMIF with Or Windows 7 32bit SUMIF with Or Office 2016
Novice
SUMIF with Or
 
Join Date: Apr 2016
Posts: 10
Lluewhyn is on a distinguished road
Default

Well, I believe I figured out a way to do it with an example at the bottom, but it wasn't as ideal as having a one-function solution.
Attached Files
File Type: xlsx SUMIFS Example.xlsx (12.3 KB, 13 views)
Reply With Quote
  #7  
Old 02-17-2017, 10:48 AM
jeffreybrown jeffreybrown is offline SUMIF with Or Windows Vista SUMIF with Or Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Does with work for you in I31...

=SUMIF(G7:G16,G31,I7:I16)+SUMIFS(I7:I16,G7:G16,"<> "&G31,H7:H16,"Bob")
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumif with Conditions Haha88 Excel 2 12-21-2016 12:08 AM
Another SUMIF OTPM Excel 6 06-06-2016 03:00 AM
VBA sumif Nisio07 Excel Programming 0 02-26-2016 07:53 AM
Need help sumif with variable for VBA jingo Excel Programming 4 01-23-2014 11:02 AM
SUMIF with Or Average if and sumif jennamae Excel 4 01-17-2014 05:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:23 PM.


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