sumproduct between 2 ranges

I want to count the number of cells whose values fall between 0.40 and 0.61.
I did

=SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61))

but the result is not the same as when I manually count the number of cells
so the formula is clearly wrong.

how do I fix?


--
Message posted via http://www.officekb.com

One way:

=SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))

=sumproduct() likes to work with numbers. The -- changes true/false to +1/0.

"Patty via OfficeKB.com" wrote:
>
> I want to count the number of cells whose values fall between 0.40 and 0.61.
> I did
>
> =SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61))
>
> but the result is not the same as when I manually count the number of cells
> so the formula is clearly wrong.
>
> how do I fix?
>
> --
> Message posted via http://www.officekb.com

--

Dave Peterson

Dave,

Side note. For some reason, if you multiply the expressions yourself (use
an asterisk multiply operator), the resulting TRUE or FALSE values of each
boolean expression get coerced to 1 or 0 automatically, and you don't need
the double negation operators:

=SUMPRODUCT( (M3:M20>0.4) * (M3:M20<0.61) )

In this case we're not really using the PRODUCT part of SUMPRODUCT, since
there's only one argument. It's just a handy array-SUM function.

But if we provide it with two arguments, letting it do the multiplication:

=SUMPRODUCT( (M3:M20>0.4), (M3:M20<0.61) )

It doesn't seem to coerce the resulting TRUE or FALSE values to 1 or 0
before it does the multiply. So we have to use the double-negation to force
the coercion.

=SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))

I don't know why. I'm just a pawn in the great game of life.

--
Earl Kiosterud
www.smokeylake.com

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42D57C90.2D9C44A5@verizonXSPAM.net...
> One way:
>
> =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))
>
> =sumproduct() likes to work with numbers. The -- changes true/false to
> +1/0.
>
> "Patty via OfficeKB.com" wrote:
>>
>> I want to count the number of cells whose values fall between 0.40 and
>> 0.61.
>> I did
>>
>> =SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61))
>>
>> but the result is not the same as when I manually count the number of
>> cells
>> so the formula is clearly wrong.
>>
>> how do I fix?
>>
>> --
>> Message posted via http://www.officekb.com
>
> --
>
> Dave Peterson

From what I've read, the -- version is slightly faster (generally).

And I actually find it easier to understand how the product and sum work in the
=sumproduct() function!

Earl Kiosterud wrote:
>
> Dave,
>
> Side note. For some reason, if you multiply the expressions yourself (use
> an asterisk multiply operator), the resulting TRUE or FALSE values of each
> boolean expression get coerced to 1 or 0 automatically, and you don't need
> the double negation operators:
>
> =SUMPRODUCT( (M3:M20>0.4) * (M3:M20<0.61) )
>
> In this case we're not really using the PRODUCT part of SUMPRODUCT, since
> there's only one argument. It's just a handy array-SUM function.
>
> But if we provide it with two arguments, letting it do the multiplication:
>
> =SUMPRODUCT( (M3:M20>0.4), (M3:M20<0.61) )
>
> It doesn't seem to coerce the resulting TRUE or FALSE values to 1 or 0
> before it does the multiply. So we have to use the double-negation to force
> the coercion.
>
> =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))
>
> I don't know why. I'm just a pawn in the great game of life.
>
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42D57C90.2D9C44A5@verizonXSPAM.net...
> > One way:
> >
> > =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))
> >
> > =sumproduct() likes to work with numbers. The -- changes true/false to
> > +1/0.
> >
> > "Patty via OfficeKB.com" wrote:
> >>
> >> I want to count the number of cells whose values fall between 0.40 and
> >> 0.61.
> >> I did
> >>
> >> =SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61))
> >>
> >> but the result is not the same as when I manually count the number of
> >> cells
> >> so the formula is clearly wrong.
> >>
> >> how do I fix?
> >>
> >> --
> >> Message posted via http://www.officekb.com
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Dave,

I agree. It's a little weird to be using SUMPRODUCT, then still have to use
multiply operators. It also seems odd that when using two or more arguments
(your way), it doesn't coerce the TRUE and FALSE. Why do I hear Twilight
Zone music in the background?
--
Earl Kiosterud
www.smokeylake.com

Off topic: Anyone who hasn't Men in Coats,
http://www.koreus.com/files/200505/men-in-coats.html should do so. It's a
riot.

----------------------------------------------------------------------------------------

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42D661E4.E024C8B3@verizonXSPAM.net...
> From what I've read, the -- version is slightly faster (generally).
>
> And I actually find it easier to understand how the product and sum work
> in the
> =sumproduct() function!
>
> Earl Kiosterud wrote:
>>
>> Dave,
>>
>> Side note. For some reason, if you multiply the expressions yourself
>> (use
>> an asterisk multiply operator), the resulting TRUE or FALSE values of
>> each
>> boolean expression get coerced to 1 or 0 automatically, and you don't
>> need
>> the double negation operators:
>>
>> =SUMPRODUCT( (M3:M20>0.4) * (M3:M20<0.61) )
>>
>> In this case we're not really using the PRODUCT part of SUMPRODUCT, since
>> there's only one argument. It's just a handy array-SUM function.
>>
>> But if we provide it with two arguments, letting it do the
>> multiplication:
>>
>> =SUMPRODUCT( (M3:M20>0.4), (M3:M20<0.61) )
>>
>> It doesn't seem to coerce the resulting TRUE or FALSE values to 1 or 0
>> before it does the multiply. So we have to use the double-negation to
>> force
>> the coercion.
>>
>> =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))
>>
>> I don't know why. I'm just a pawn in the great game of life.
>>
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:42D57C90.2D9C44A5@verizonXSPAM.net...
>> > One way:
>> >
>> > =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61))
>> >
>> > =sumproduct() likes to work with numbers. The -- changes true/false to
>> > +1/0.
>> >
>> > "Patty via OfficeKB.com" wrote:
>> >>
>> >> I want to count the number of cells whose values fall between 0.40 and
>> >> 0.61.
>> >> I did
>> >>
>> >> =SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61))
>> >>
>> >> but the result is not the same as when I manually count the number of
>> >> cells
>> >> so the formula is clearly wrong.
>> >>
>> >> how do I fix?
>> >>
>> >> --
>> >> Message posted via http://www.officekb.com
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson