sumproduct between 2 ranges

  • Thread starter Patty via OfficeKB.com
  • Start date
P

Patty via OfficeKB.com

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?
 
D

Dave Peterson

One way:

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

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

Earl Kiosterud

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.
 
D

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 said:
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.
Click to expand...
 
E

Earl Kiosterud

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 said:
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!
Click to expand...
 
Top