If you like to have conditions hard-coded into your formulas...
=SUM(SUMIF(B5:B12,{"D*","?D*"},D5
12))
would constitute such an example. You are right about the constant
array acting as IN or as a chain of OR's.
Conditions like <250 and >125 that must kold at the same time cannot
be expressed with IN.
Some options are:
[1]
=SUMIF(ConditionRange,">"&125,SumRange)-SUMIF(ConditionRange,">="&250,SumRange)
[2]
=SUMPRODUCT(--(ConditionRange>125),--(ConditionRange<250),SumRange)
Since you are inquiring about whether a SumIf formula with an
array-constant which would work with <250 and >125...
If we look at how [1] is built, we can derive:
=SUM(SUMIF(ConditionRange,{">125",">=250"),SumRange)*{1,-1})
which could be useful in some circumstances. A variablized version
would be:
=SUMPRODUCT(SUMIF(ConditionRange,X2:Y2,SumRange)*{1,-1})
where X2 literally houses >125 and Y2 >=250. Trying to variablize [2]
the same way would not allow specifying X2:Y2 in the foregoing manner.
C said:
Somewhat like an implicit OR but it's actually an implicity IN! I'm
wondering if there are any other possibilities, i.e.: <250, >125.
This doesn't work with the curlies.
Thanks.
Aladin Akyurek wrote:
=SUM(SUMIF(B5:B12,{"D*","?D*"},D5
12))
C Glenn wrote:
I'm trying to do this:
=SUMIF(B5:B12,OR("=D*","=?D*"),D5
12)
It returns zero each time.
(BTW,
=SUMIF(B5:B12,"=D*",D5
12)
and
=SUMIF(B5:B12,"=?D*",D5
12)
both work just fine and return non zero sums.)
So my question is,
Where do I put the OR, or is there another way to do this?