I've tried and never been able to find a better solution than simply using:
=SUMIF(B5:B12,"=D*",D5:D12)+SUMIF(B5:B12,"=?D*",D5:D12)
"C Glenn" wrote:
> I'm trying to do this:
>
> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>
> It returns zero each time.
>
> (BTW,
> =SUMIF(B5:B12,"=D*",D5:D12)
> and
> =SUMIF(B5:B12,"=?D*",D5:D12)
> 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?
>
=SUM(SUMIF(B5:B12,{"D*","?D*"},D5:D12))
C Glenn wrote:
> I'm trying to do this:
>
> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>
> It returns zero each time.
>
> (BTW,
> =SUMIF(B5:B12,"=D*",D5:D12)
> and
> =SUMIF(B5:B12,"=?D*",D5:D12)
> 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?
Aladin,
Excellent. I have never seen that use of the Sumif before where it returns
an array of values.
Alok
"Aladin Akyurek" wrote:
> =SUM(SUMIF(B5:B12,{"D*","?D*"},D5:D12))
>
> C Glenn wrote:
> > I'm trying to do this:
> >
> > =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
> >
> > It returns zero each time.
> >
> > (BTW,
> > =SUMIF(B5:B12,"=D*",D5:D12)
> > and
> > =SUMIF(B5:B12,"=?D*",D5:D12)
> > 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?
>
I'm trying to do this:
=SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
It returns zero each time.
(BTW,
=SUMIF(B5:B12,"=D*",D5:D12)
and
=SUMIF(B5:B12,"=?D*",D5:D12)
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?
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:D12))
>
> C Glenn wrote:
>
>> I'm trying to do this:
>>
>> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>>
>> It returns zero each time.
>>
>> (BTW,
>> =SUMIF(B5:B12,"=D*",D5:D12)
>> and
>> =SUMIF(B5:B12,"=?D*",D5:D12)
>> 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?
Glenn,
This does work but you have to enclose them in quotes. {"<100",">250"}
Alok
"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*"},D5:D12))
> >
> > C Glenn wrote:
> >
> >> I'm trying to do this:
> >>
> >> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
> >>
> >> It returns zero each time.
> >>
> >> (BTW,
> >> =SUMIF(B5:B12,"=D*",D5:D12)
> >> and
> >> =SUMIF(B5:B12,"=?D*",D5:D12)
> >> 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?
>
I can't get this to work. In every case, the first criteria is applied
and the second is ignored.
Put these numbers in D, starting at D5:
45
123
789
4156
123
456
987
321
Now put these in G, starting at G5:
65
32
8546
123
646
564
6
54
If you put the following in G13:
=SUMIF(D5:D12, {"<900",">100"},G5:G12)
it will return the same result as
=SUMIF(D5:D12, {"<900"},G5:G12).
The result of the first formula should be 65 less than the second.
Alok wrote:
> Glenn,
> This does work but you have to enclose them in quotes. {"<100",">250"}
> Alok
>
> "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*"},D5:D12))
>>>
>>>C Glenn wrote:
>>>
>>>
>>>>I'm trying to do this:
>>>>
>>>>=SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>>>>
>>>>It returns zero each time.
>>>>
>>>>(BTW,
>>>>=SUMIF(B5:B12,"=D*",D5:D12)
>>>>and
>>>>=SUMIF(B5:B12,"=?D*",D5:D12)
>>>>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?
>>
OK, wait a minute. I did say OR originally; so, let me rephrase.
Instead of "The result of the first formula should be 65 less than the
second", how about "wouldn't it be swell if we could implement this with
a logical AND so that the result of the..."
C Glenn wrote:
> I can't get this to work. In every case, the first criteria is applied
> and the second is ignored.
>
> Put these numbers in D, starting at D5:
>
> 45
> 123
> 789
> 4156
> 123
> 456
> 987
> 321
>
>
> Now put these in G, starting at G5:
>
> 65
> 32
> 8546
> 123
> 646
> 564
> 6
> 54
>
> If you put the following in G13:
> =SUMIF(D5:D12, {"<900",">100"},G5:G12)
> it will return the same result as
> =SUMIF(D5:D12, {"<900"},G5:G12).
>
> The result of the first formula should be 65 less than the second.
>
>
>
>
>
>
> Alok wrote:
>
>> Glenn,
>> This does work but you have to enclose them in quotes. {"<100",">250"}
>> Alok
>>
>> "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*"},D5:D12))
>>>>
>>>> C Glenn wrote:
>>>>
>>>>
>>>>> I'm trying to do this:
>>>>>
>>>>> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>>>>>
>>>>> It returns zero each time.
>>>>>
>>>>> (BTW,
>>>>> =SUMIF(B5:B12,"=D*",D5:D12)
>>>>> and
>>>>> =SUMIF(B5:B12,"=?D*",D5:D12)
>>>>> 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?
>>>
>>>
Try...
=SUMPRODUCT(--(D5:D12>100),--(D5:D12<900),G5:G12)
and
=SUMIF(D5:D12,"<900",G5:G12)
Hope this helps!
In article <[email protected]>,
C Glenn <[email protected]> wrote:
> I can't get this to work. In every case, the first criteria is applied
> and the second is ignored.
>
> Put these numbers in D, starting at D5:
>
> 45
> 123
> 789
> 4156
> 123
> 456
> 987
> 321
>
>
> Now put these in G, starting at G5:
>
> 65
> 32
> 8546
> 123
> 646
> 564
> 6
> 54
>
> If you put the following in G13:
> =SUMIF(D5:D12, {"<900",">100"},G5:G12)
> it will return the same result as
> =SUMIF(D5:D12, {"<900"},G5:G12).
>
> The result of the first formula should be 65 less than the second.
>
Hi
This is happening because both functions are returning an array. The first
function returns a single element in the array. The second function returns a
two element array. If there is no function that operates on the array of
values returned then Excel tends to use the first value of the array.
If you enclose your Sumif within a Sum function like Aladin had done you
will see that there is a difference. The second function will then return a
number which is interpreted as the Sum of all numbers where D5:D12 is less
than 900 plus some of all numbers where the range D5:D12 is greater than 100.
This is different than the sum of all numbers where D5:D12 is greater than
100 or less than 900 (which is basically all numbers.) In other words the
method shown by Aladin has to be used cautiously - that is only in case of
non-overlapping ranges. In other words this is probably OK
=SUM(SUMIF(D5:D12, {"<100",">900"},G5:G12))
if one intends to find the Sum of all numbers in G5 to G12 where the numbers
in D5:D12 are either smaller than 100 or greater than 900.
Alok
"C Glenn" wrote:
> I can't get this to work. In every case, the first criteria is applied
> and the second is ignored.
>
> Put these numbers in D, starting at D5:
>
> 45
> 123
> 789
> 4156
> 123
> 456
> 987
> 321
>
>
> Now put these in G, starting at G5:
>
> 65
> 32
> 8546
> 123
> 646
> 564
> 6
> 54
>
> If you put the following in G13:
> =SUMIF(D5:D12, {"<900",">100"},G5:G12)
> it will return the same result as
> =SUMIF(D5:D12, {"<900"},G5:G12).
>
> The result of the first formula should be 65 less than the second.
>
>
>
>
>
>
> Alok wrote:
> > Glenn,
> > This does work but you have to enclose them in quotes. {"<100",">250"}
> > Alok
> >
> > "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*"},D5:D12))
> >>>
> >>>C Glenn wrote:
> >>>
> >>>
> >>>>I'm trying to do this:
> >>>>
> >>>>=SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
> >>>>
> >>>>It returns zero each time.
> >>>>
> >>>>(BTW,
> >>>>=SUMIF(B5:B12,"=D*",D5:D12)
> >>>>and
> >>>>=SUMIF(B5:B12,"=?D*",D5:D12)
> >>>>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?
> >>
>
This is precisely what I was looking for. Years ago this question came
up and I recall now that SUMPRODUCT was the answer; but the
implementation looked different.
I don't understand the syntax. The help text on SUMPRODUCT states that
it is an array multiplication function. I've looked through Google for
a more complete explanation of what it will do and how to use it, but I
haven't found anything comprehensive. Do you know of a source?
Thanks.
Domenic wrote:
> Try...
>
> =SUMPRODUCT(--(D5:D12>100),--(D5:D12<900),G5:G12)
>
> and
>
> =SUMIF(D5:D12,"<900",G5:G12)
>
> Hope this helps!
>
> In article <[email protected]>,
> C Glenn <[email protected]> wrote:
>
>
>>I can't get this to work. In every case, the first criteria is applied
>>and the second is ignored.
>>
>>Put these numbers in D, starting at D5:
>>
>>45
>>123
>>789
>>4156
>>123
>>456
>>987
>>321
>>
>>
>>Now put these in G, starting at G5:
>>
>>65
>>32
>>8546
>>123
>>646
>>564
>>6
>>54
>>
>>If you put the following in G13:
>>=SUMIF(D5:D12, {"<900",">100"},G5:G12)
>>it will return the same result as
>>=SUMIF(D5:D12, {"<900"},G5:G12).
>>
>>The result of the first formula should be 65 less than the second.
>>
Hi
Take a look at Bob Phillips discussion document on this at
http://xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Roger Govier
C Glenn wrote:
> This is precisely what I was looking for. Years ago this question
> came up and I recall now that SUMPRODUCT was the answer; but the
> implementation looked different.
>
> I don't understand the syntax. The help text on SUMPRODUCT states
> that it is an array multiplication function. I've looked through
> Google for a more complete explanation of what it will do and how to
> use it, but I haven't found anything comprehensive. Do you know of a
> source?
>
> Thanks.
>
> Domenic wrote:
>
>> Try...
>>
>> =SUMPRODUCT(--(D5:D12>100),--(D5:D12<900),G5:G12)
>>
>> and
>>
>> =SUMIF(D5:D12,"<900",G5:G12)
>>
>> Hope this helps!
>>
>> In article <[email protected]>,
>> C Glenn <[email protected]> wrote:
>>
>>
>>> I can't get this to work. In every case, the first criteria is
>>> applied and the second is ignored.
>>>
>>> Put these numbers in D, starting at D5:
>>>
>>> 45
>>> 123
>>> 789
>>> 4156
>>> 123
>>> 456
>>> 987
>>> 321
>>>
>>>
>>> Now put these in G, starting at G5:
>>>
>>> 65
>>> 32
>>> 8546
>>> 123
>>> 646
>>> 564
>>> 6
>>> 54
>>>
>>> If you put the following in G13:
>>> =SUMIF(D5:D12, {"<900",">100"},G5:G12)
>>> it will return the same result as
>>> =SUMIF(D5:D12, {"<900"},G5:G12).
>>>
>>> The result of the first formula should be 65 less than the second.
>>>
You are right. This is exactly what I said in my last post.
The formula
=SUM(SUMIF(D5:D12, {">100","<900"}, G5:G12))
translates to
SUMIF(D5:D12, ">100", G5:G12)+SUMIF(D5:D12, "<900", G5:G12))
Alok
"C Glenn" wrote:
> I've tried this:
>
> =SUM(SUMIF(D5:D12, {">100","<900"}, G5:G12))
> but I get the sum of all the numbers >100 (including those >900) and the
> sum of all the numbers <900 (including those <100).
>
> Am I misunderstanding something?
>
> Alok wrote:
> > Hi
> > This is happening because both functions are returning an array. The first
> > function returns a single element in the array. The second function returns a
> > two element array. If there is no function that operates on the array of
> > values returned then Excel tends to use the first value of the array.
> > If you enclose your Sumif within a Sum function like Aladin had done you
> > will see that there is a difference. The second function will then return a
> > number which is interpreted as the Sum of all numbers where D5:D12 is less
> > than 900 plus some of all numbers where the range D5:D12 is greater than 100.
> > This is different than the sum of all numbers where D5:D12 is greater than
> > 100 or less than 900 (which is basically all numbers.) In other words the
> > method shown by Aladin has to be used cautiously - that is only in case of
> > non-overlapping ranges. In other words this is probably OK
> >
> > =SUM(SUMIF(D5:D12, {"<100",">900"},G5:G12))
> >
> > if one intends to find the Sum of all numbers in G5 to G12 where the numbers
> > in D5:D12 are either smaller than 100 or greater than 900.
> >
> > Alok
> >
> >
> >
> >
> > "C Glenn" wrote:
> >
> >
> >>I can't get this to work. In every case, the first criteria is applied
> >>and the second is ignored.
> >>
> >>Put these numbers in D, starting at D5:
> >>
> >>45
> >>123
> >>789
> >>4156
> >>123
> >>456
> >>987
> >>321
> >>
> >>
> >>Now put these in G, starting at G5:
> >>
> >>65
> >>32
> >>8546
> >>123
> >>646
> >>564
> >>6
> >>54
> >>
> >>If you put the following in G13:
> >>=SUMIF(D5:D12, {"<900",">100"},G5:G12)
> >>it will return the same result as
> >>=SUMIF(D5:D12, {"<900"},G5:G12).
> >>
> >>The result of the first formula should be 65 less than the second.
> >>
> >>
> >>
> >>
> >>
> >>
> >>Alok wrote:
> >>
> >>>Glenn,
> >>>This does work but you have to enclose them in quotes. {"<100",">250"}
> >>>Alok
> >>>
> >>>"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*"},D5:D12))
> >>>>>
> >>>>>C Glenn wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>>I'm trying to do this:
> >>>>>>
> >>>>>>=SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
> >>>>>>
> >>>>>>It returns zero each time.
> >>>>>>
> >>>>>>(BTW,
> >>>>>>=SUMIF(B5:B12,"=D*",D5:D12)
> >>>>>>and
> >>>>>>=SUMIF(B5:B12,"=?D*",D5:D12)
> >>>>>>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?
> >>>>
>
I've tried this:
=SUM(SUMIF(D5:D12, {">100","<900"}, G5:G12))
but I get the sum of all the numbers >100 (including those >900) and the
sum of all the numbers <900 (including those <100).
Am I misunderstanding something?
Alok wrote:
> Hi
> This is happening because both functions are returning an array. The first
> function returns a single element in the array. The second function returns a
> two element array. If there is no function that operates on the array of
> values returned then Excel tends to use the first value of the array.
> If you enclose your Sumif within a Sum function like Aladin had done you
> will see that there is a difference. The second function will then return a
> number which is interpreted as the Sum of all numbers where D5:D12 is less
> than 900 plus some of all numbers where the range D5:D12 is greater than 100.
> This is different than the sum of all numbers where D5:D12 is greater than
> 100 or less than 900 (which is basically all numbers.) In other words the
> method shown by Aladin has to be used cautiously - that is only in case of
> non-overlapping ranges. In other words this is probably OK
>
> =SUM(SUMIF(D5:D12, {"<100",">900"},G5:G12))
>
> if one intends to find the Sum of all numbers in G5 to G12 where the numbers
> in D5:D12 are either smaller than 100 or greater than 900.
>
> Alok
>
>
>
>
> "C Glenn" wrote:
>
>
>>I can't get this to work. In every case, the first criteria is applied
>>and the second is ignored.
>>
>>Put these numbers in D, starting at D5:
>>
>>45
>>123
>>789
>>4156
>>123
>>456
>>987
>>321
>>
>>
>>Now put these in G, starting at G5:
>>
>>65
>>32
>>8546
>>123
>>646
>>564
>>6
>>54
>>
>>If you put the following in G13:
>>=SUMIF(D5:D12, {"<900",">100"},G5:G12)
>>it will return the same result as
>>=SUMIF(D5:D12, {"<900"},G5:G12).
>>
>>The result of the first formula should be 65 less than the second.
>>
>>
>>
>>
>>
>>
>>Alok wrote:
>>
>>>Glenn,
>>>This does work but you have to enclose them in quotes. {"<100",">250"}
>>>Alok
>>>
>>>"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*"},D5:D12))
>>>>>
>>>>>C Glenn wrote:
>>>>>
>>>>>
>>>>>
>>>>>>I'm trying to do this:
>>>>>>
>>>>>>=SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>>>>>>
>>>>>>It returns zero each time.
>>>>>>
>>>>>>(BTW,
>>>>>>=SUMIF(B5:B12,"=D*",D5:D12)
>>>>>>and
>>>>>>=SUMIF(B5:B12,"=?D*",D5:D12)
>>>>>>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?
>>>>
If you like to have conditions hard-coded into your formulas...
=SUM(SUMIF(B5:B12,{"D*","?D*"},D5:D12))
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 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*"},D5:D12))
>>
>> C Glenn wrote:
>>
>>> I'm trying to do this:
>>>
>>> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>>>
>>> It returns zero each time.
>>>
>>> (BTW,
>>> =SUMIF(B5:B12,"=D*",D5:D12)
>>> and
>>> =SUMIF(B5:B12,"=?D*",D5:D12)
>>> 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?
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Thank you again. I'm beginning to absorb this.
Aladin Akyurek wrote:
> If you like to have conditions hard-coded into your formulas...
>
> =SUM(SUMIF(B5:B12,{"D*","?D*"},D5:D12))
>
> 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 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*"},D5:D12))
>>>
>>> C Glenn wrote:
>>>
>>>> I'm trying to do this:
>>>>
>>>> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>>>>
>>>> It returns zero each time.
>>>>
>>>> (BTW,
>>>> =SUMIF(B5:B12,"=D*",D5:D12)
>>>> and
>>>> =SUMIF(B5:B12,"=?D*",D5:D12)
>>>> 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?
>
>
Thanks.
Alok wrote:
> You are right. This is exactly what I said in my last post.
> The formula
> =SUM(SUMIF(D5:D12, {">100","<900"}, G5:G12))
> translates to
> SUMIF(D5:D12, ">100", G5:G12)+SUMIF(D5:D12, "<900", G5:G12))
> Alok
>
>
> "C Glenn" wrote:
>
>
>>I've tried this:
>>
>>=SUM(SUMIF(D5:D12, {">100","<900"}, G5:G12))
>>but I get the sum of all the numbers >100 (including those >900) and the
>>sum of all the numbers <900 (including those <100).
>>
>>Am I misunderstanding something?
>>
>>Alok wrote:
>>
>>>Hi
>>>This is happening because both functions are returning an array. The first
>>>function returns a single element in the array. The second function returns a
>>>two element array. If there is no function that operates on the array of
>>>values returned then Excel tends to use the first value of the array.
>>>If you enclose your Sumif within a Sum function like Aladin had done you
>>>will see that there is a difference. The second function will then return a
>>>number which is interpreted as the Sum of all numbers where D5:D12 is less
>>>than 900 plus some of all numbers where the range D5:D12 is greater than 100.
>>>This is different than the sum of all numbers where D5:D12 is greater than
>>>100 or less than 900 (which is basically all numbers.) In other words the
>>>method shown by Aladin has to be used cautiously - that is only in case of
>>>non-overlapping ranges. In other words this is probably OK
>>>
>>>=SUM(SUMIF(D5:D12, {"<100",">900"},G5:G12))
>>>
>>>if one intends to find the Sum of all numbers in G5 to G12 where the numbers
>>>in D5:D12 are either smaller than 100 or greater than 900.
>>>
>>>Alok
>>>
>>>
>>>
>>>
>>>"C Glenn" wrote:
>>>
>>>
>>>
>>>>I can't get this to work. In every case, the first criteria is applied
>>>>and the second is ignored.
>>>>
>>>>Put these numbers in D, starting at D5:
>>>>
>>>>45
>>>>123
>>>>789
>>>>4156
>>>>123
>>>>456
>>>>987
>>>>321
>>>>
>>>>
>>>>Now put these in G, starting at G5:
>>>>
>>>>65
>>>>32
>>>>8546
>>>>123
>>>>646
>>>>564
>>>>6
>>>>54
>>>>
>>>>If you put the following in G13:
>>>>=SUMIF(D5:D12, {"<900",">100"},G5:G12)
>>>>it will return the same result as
>>>>=SUMIF(D5:D12, {"<900"},G5:G12).
>>>>
>>>>The result of the first formula should be 65 less than the second.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>Alok wrote:
>>>>
>>>>
>>>>>Glenn,
>>>>>This does work but you have to enclose them in quotes. {"<100",">250"}
>>>>>Alok
>>>>>
>>>>>"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*"},D5:D12))
>>>>>>>
>>>>>>>C Glenn wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>I'm trying to do this:
>>>>>>>>
>>>>>>>>=SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>>>>>>>>
>>>>>>>>It returns zero each time.
>>>>>>>>
>>>>>>>>(BTW,
>>>>>>>>=SUMIF(B5:B12,"=D*",D5:D12)
>>>>>>>>and
>>>>>>>>=SUMIF(B5:B12,"=?D*",D5:D12)
>>>>>>>>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?
>>>>>>
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*"},D5:D12))
>
> 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 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*"},D5:D12))
>>>
>>> C Glenn wrote:
>>>
>>>> I'm trying to do this:
>>>>
>>>> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>>>>
>>>> It returns zero each time.
>>>>
>>>> (BTW,
>>>> =SUMIF(B5:B12,"=D*",D5:D12)
>>>> and
>>>> =SUMIF(B5:B12,"=?D*",D5:D12)
>>>> 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?
>
>
in this formula
=SUMPRODUCT(--(ConditionRange>125),--(ConditionRange<250),SumRange)
leave of the argument SumRange and you will get the count
=SUMPRODUCT(--(ConditionRange>125),--(ConditionRange<250))
--
Regards,
Tom Ogilvy
"C Glenn" <[email protected]> wrote in message
news:%[email protected]...
> 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*"},D5:D12))
> >
> > 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,SumRan
ge)
> >
> >
> > [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 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*"},D5:D12))
> >>>
> >>> C Glenn wrote:
> >>>
> >>>> I'm trying to do this:
> >>>>
> >>>> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
> >>>>
> >>>> It returns zero each time.
> >>>>
> >>>> (BTW,
> >>>> =SUMIF(B5:B12,"=D*",D5:D12)
> >>>> and
> >>>> =SUMIF(B5:B12,"=?D*",D5:D12)
> >>>> 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?
> >
> >
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*"},D5:D12))
>>
>> 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 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*"},D5:D12))
>>>>
>>>> C Glenn wrote:
>>>>
>>>>> I'm trying to do this:
>>>>>
>>>>> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>>>>>
>>>>> It returns zero each time.
>>>>>
>>>>> (BTW,
>>>>> =SUMIF(B5:B12,"=D*",D5:D12)
>>>>> and
>>>>> =SUMIF(B5:B12,"=?D*",D5:D12)
>>>>> 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?
>>
>>
>>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
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*"},D5:D12))
>>>
>>> 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 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*"},D5:D12))
>>>>>
>>>>> C Glenn wrote:
>>>>>
>>>>>> I'm trying to do this:
>>>>>>
>>>>>> =SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
>>>>>>
>>>>>> It returns zero each time.
>>>>>>
>>>>>> (BTW,
>>>>>> =SUMIF(B5:B12,"=D*",D5:D12)
>>>>>> and
>>>>>> =SUMIF(B5:B12,"=?D*",D5:D12)
>>>>>> 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(--(ConditionRange>125),--(ConditionRange<250),SumRange)
>
> leave of the argument SumRange and you will get the count
>
> =SUMPRODUCT(--(ConditionRange>125),--(ConditionRange<250))
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks