#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Sometimes the messy details help.
Without any details, does this example help... =SUM(SUMIF(A2:A13,{"Jan","Feb","Mar"},B2:B13)) |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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?
|
#5
|
|||
|
|||
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.
|
#6
|
|||
|
|||
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.
|
#7
|
|||
|
|||
Does with work for you in I31...
=SUMIF(G7:G16,G31,I7:I16)+SUMIFS(I7:I16,G7:G16,"<> "&G31,H7:H16,"Bob") |
|
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 |
Average if and sumif | jennamae | Excel | 4 | 01-17-2014 05:10 AM |