Count of Number Values Greater Than, Less Than
 

How do you perform this count on a range of cells?

CountIf and SumProduct are both returning the wrong total, in more than one
column.

Count of Number Values Greater Than, Less Than
 

These should work...
=COUNTIF(A1:A10,"=7")
=SUMPRODUCT(--(A1:A10=7))

If those don't work your values might be formatted as text, in which case try:
=SUMPRODUCT(--(--A1:A10=7))

--
Regards,
Dave


"Anne" wrote:

How do you perform this count on a range of cells?

CountIf and SumProduct are both returning the wrong total, in more than one
column.

Count of Number Values Greater Than, Less Than
 

Thanks, but I need =0 AND <=36. It's the AND part that screws it all up.

=COUNTIF(D2:D9,"=0")-COUNTIF(D2:D9,"=<36") returns the wrong value. So
does =SUMPRODUCT(--(D2:D9=0)--(D2:D9<=36))

Values are all formated as numbers. I can't figure out what it's counting.

I just discovered that =SUM(IF((D2:D90)*(D2:D9<=36),1,0)) using
ctrl+shift+enter works. I'm grateful that it does, but why does it perform a
count when the function is to sum?

"David Billigmeier" wrote:


These should work...
=COUNTIF(A1:A10,"=7")
=SUMPRODUCT(--(A1:A10=7))

If those don't work your values might be formatted as text, in which case try:
=SUMPRODUCT(--(--A1:A10=7))

--
Regards,
Dave


"Anne" wrote:

How do you perform this count on a range of cells?

CountIf and SumProduct are both returning the wrong total, in more than one
column.

Count of Number Values Greater Than, Less Than
 

Try this:

=SUMPRODUCT(--(D2:D90),--(D2:D9<36))

HTH,
Paul

"Anne" wrote in message
...
Thanks, but I need =0 AND <=36. It's the AND part that screws it all up.

=COUNTIF(D2:D9,"=0")-COUNTIF(D2:D9,"=<36") returns the wrong value.
So
does =SUMPRODUCT(--(D2:D9=0)--(D2:D9<=36))

Values are all formated as numbers. I can't figure out what it's
counting.

I just discovered that =SUM(IF((D2:D90)*(D2:D9<=36),1,0)) using
ctrl+shift+enter works. I'm grateful that it does, but why does it
perform a
count when the function is to sum?

"David Billigmeier" wrote:


These should work...
=COUNTIF(A1:A10,"=7")
=SUMPRODUCT(--(A1:A10=7))

If those don't work your values might be formatted as text, in which case
try:
=SUMPRODUCT(--(--A1:A10=7))

--
Regards,
Dave


"Anne" wrote:

How do you perform this count on a range of cells?

CountIf and SumProduct are both returning the wrong total, in more than
one
column.

Count of Number Values Greater Than, Less Than
 

maybe
=countif(d2:d9,"=0")-countif(d2:d9,"36")
This way you are counting all values =0 and subtracting those values that
are greater then the range you don't want to include.
--
JNW


"Anne" wrote:

Thanks, but I need =0 AND <=36. It's the AND part that screws it all up.

=COUNTIF(D2:D9,"=0")-COUNTIF(D2:D9,"=<36") returns the wrong value. So
does =SUMPRODUCT(--(D2:D9=0)--(D2:D9<=36))

Values are all formated as numbers. I can't figure out what it's counting.

I just discovered that =SUM(IF((D2:D90)*(D2:D9<=36),1,0)) using
ctrl+shift+enter works. I'm grateful that it does, but why does it perform a
count when the function is to sum?

"David Billigmeier" wrote:


These should work...
=COUNTIF(A1:A10,"=7")
=SUMPRODUCT(--(A1:A10=7))

If those don't work your values might be formatted as text, in which case try:
=SUMPRODUCT(--(--A1:A10=7))

--
Regards,
Dave


"Anne" wrote:

How do you perform this count on a range of cells?

CountIf and SumProduct are both returning the wrong total, in more than one
column.

Count of Number Values Greater Than, Less Than
 

Oops! I forgot about the greater than or eaqual to:

=SUMPRODUCT(--(D2:D9=0),--(D2:D9<37))



"PCLIVE" wrote in message
...
Try this:

=SUMPRODUCT(--(D2:D90),--(D2:D9<36))

HTH,
Paul

"Anne" wrote in message
...
Thanks, but I need =0 AND <=36. It's the AND part that screws it all
up.

=COUNTIF(D2:D9,"=0")-COUNTIF(D2:D9,"=<36") returns the wrong value. So
does =SUMPRODUCT(--(D2:D9=0)--(D2:D9<=36))

Values are all formated as numbers. I can't figure out what it's
counting.

I just discovered that =SUM(IF((D2:D90)*(D2:D9<=36),1,0)) using
ctrl+shift+enter works. I'm grateful that it does, but why does it
perform a
count when the function is to sum?

"David Billigmeier" wrote:


These should work...
=COUNTIF(A1:A10,"=7")
=SUMPRODUCT(--(A1:A10=7))

If those don't work your values might be formatted as text, in which
case try:
=SUMPRODUCT(--(--A1:A10=7))

--
Regards,
Dave


"Anne" wrote:

How do you perform this count on a range of cells?

CountIf and SumProduct are both returning the wrong total, in more
than one
column.

Count of Number Values Greater Than, Less Than
 

To answer your question of why SUM(IF... counts:

Starting with the IF((D2:D90)*(D2:D9<=36), these two conditional checks
return arrays of 1 and 0 (actually, they return TRUE and FALSE which are
then coerced into 1 and 0 through the multiplication). When multiplied,
these two arrays create one array of 1s and 0s.

So now you have something like IF({1,1,0,1,0,0,0,1}...or whatever the
results would actually be. Each of these elements are evaluated such as
IF(1,1,0), IF(1,1,0), IF(0,1,0), etc. resulting in a further array of 1s and
0s. This final array is then summed, giving results that mimick a
COUNT-type function.

If you want to see this in action select the cell, then go to Tools,Formula
Auditing, Evaluate Formula.

"Anne" wrote in message
...
Thanks, but I need =0 AND <=36. It's the AND part that screws it all up.

=COUNTIF(D2:D9,"=0")-COUNTIF(D2:D9,"=<36") returns the wrong value.
So
does =SUMPRODUCT(--(D2:D9=0)--(D2:D9<=36))

Values are all formated as numbers. I can't figure out what it's
counting.

I just discovered that =SUM(IF((D2:D90)*(D2:D9<=36),1,0)) using
ctrl+shift+enter works. I'm grateful that it does, but why does it
perform a
count when the function is to sum?

"David Billigmeier" wrote:


These should work...
=COUNTIF(A1:A10,"=7")
=SUMPRODUCT(--(A1:A10=7))

If those don't work your values might be formatted as text, in which case
try:
=SUMPRODUCT(--(--A1:A10=7))

--
Regards,
Dave


"Anne" wrote:

How do you perform this count on a range of cells?

CountIf and SumProduct are both returning the wrong total, in more than
one
column.