sumif with or

Thanks Aladin. This is very helpful.

Chris.

Aladin Akyurek wrote:
1.

=SUM(COUNTIF(B5:B12,{"D*", "A*"}))

And: With E2 housing the D condition and F2 the A condition...

2.

=SUMPRODUCT(COUNTIF(B5:B12,E2:F2&"*"))

3.

=SUMPRODUCT(--ISNUMBER(MATCH(LEFT(B5:B12),E2:F2,0)))

C Glenn wrote:

Hey, one more question in this regard. Suppose I wanted to count the
number of cells in the same rows as those that match the specified
criteria (instead of adding them), could I do that? (I need to.)

I tried =COUNT(SUMPRODUCT(... but that returned 1.

I also tried =COUNTIF(... but I can't get it to respect more than one
criteria.
=COUNTIF(B5:B12, {"D*", "A*"})
returned the same result as
=COUNTIF(B5:B12, {"D*"})

Any ideas on this?


Aladin Akyurek wrote:

If you like to have conditions hard-coded into your formulas...

=SUM(SUMIF(B5:B12,{"D*","?D*"},D512))

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 a

[1]

=SUMIF(ConditionRange,""&125,SumRange)-SUMIF(ConditionRange,"="&250,SumRange)


[2]

=SUMPRODUCT(--(ConditionRange125),--(ConditionRange250),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"),SumRang e)*{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 Glenn wrote:

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*"},D512))

C Glenn wrote:

I'm trying to do this:

=SUMIF(B5:B12,OR("=D*","=?D*"),D512)

It returns zero each time.

(BTW,
=SUMIF(B5:B12,"=D*",D512)
and
=SUMIF(B5:B12,"=?D*",D512)
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?

Worked perfectly. Found I could get it to respect text comparisons as well.

Thanks, Chris.

Tom Ogilvy wrote:
in this formula
=SUMPRODUCT(--(ConditionRange125),--(ConditionRange250),SumRange)

leave of the argument SumRange and you will get the count

=SUMPRODUCT(--(ConditionRange125),--(ConditionRange250))