software

I am using sumproduct to count a total number of cases. The formula I have
been using is:

=SUMPRODUCT((report!$Y$4:$Y$400lt;60)*(report!$Y$4:$ Y$400gt;-60))

I have some cells that have a value of 0, and some are blank. I don't want
to count the cells that are blank, but I do want the cells with zero counted.

How would I do this?

Thanks in advance.

Hi!

Try this:

=SUMPRODUCT((ISNUMBER(report!$Y$4:$Y$400))*(report !$Y$4:$Y$400lt;60)*(report!$Y$4:$Y$400gt;-60))

Biff

quot;Mattquot; gt; wrote in message
...
gt;I am using sumproduct to count a total number of cases. The formula I have
gt; been using is:
gt;
gt; =SUMPRODUCT((report!$Y$4:$Y$400lt;60)*(report!$Y$4:$ Y$400gt;-60))
gt;
gt; I have some cells that have a value of 0, and some are blank. I don't
gt; want
gt; to count the cells that are blank, but I do want the cells with zero
gt; counted.
gt;
gt; How would I do this?
gt;
gt; Thanks in advance.
=SUMPRODUCT(--(report!$Y$4:$Y$400gt;-60),--(report!$Y$4:$Y$400lt;60),--(report!$
Y$4:$Y$400lt;gt;quot;quot;))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Mattquot; gt; wrote in message
...
gt; I am using sumproduct to count a total number of cases. The formula I
have
gt; been using is:
gt;
gt; =SUMPRODUCT((report!$Y$4:$Y$400lt;60)*(report!$Y$4:$ Y$400gt;-60))
gt;
gt; I have some cells that have a value of 0, and some are blank. I don't
want
gt; to count the cells that are blank, but I do want the cells with zero
counted.
gt;
gt; How would I do this?
gt;
gt; Thanks in advance.
Thanks! This worked like I wanted it to.

I really appreciate it.

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT((ISNUMBER(report!$Y$4:$Y$400))*(report !$Y$4:$Y$400lt;60)*(report!$Y$4:$Y$400gt;-60))
gt;
gt; Biff
gt;
gt; quot;Mattquot; gt; wrote in message
gt; ...
gt; gt;I am using sumproduct to count a total number of cases. The formula I have
gt; gt; been using is:
gt; gt;
gt; gt; =SUMPRODUCT((report!$Y$4:$Y$400lt;60)*(report!$Y$4:$ Y$400gt;-60))
gt; gt;
gt; gt; I have some cells that have a value of 0, and some are blank. I don't
gt; gt; want
gt; gt; to count the cells that are blank, but I do want the cells with zero
gt; gt; counted.
gt; gt;
gt; gt; How would I do this?
gt; gt;
gt; gt; Thanks in advance.
gt;
gt;
gt;

You're welcome. Thanks for the feedback!

Biff

quot;Mattquot; gt; wrote in message
...
gt; Thanks! This worked like I wanted it to.
gt;
gt; I really appreciate it.
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; =SUMPRODUCT((ISNUMBER(report!$Y$4:$Y$400))*(report !$Y$4:$Y$400lt;60)*(report!$Y$4:$Y$400gt;-60))
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Mattquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am using sumproduct to count a total number of cases. The formula I
gt;gt; gt;have
gt;gt; gt; been using is:
gt;gt; gt;
gt;gt; gt; =SUMPRODUCT((report!$Y$4:$Y$400lt;60)*(report!$Y$4:$ Y$400gt;-60))
gt;gt; gt;
gt;gt; gt; I have some cells that have a value of 0, and some are blank. I don't
gt;gt; gt; want
gt;gt; gt; to count the cells that are blank, but I do want the cells with zero
gt;gt; gt; counted.
gt;gt; gt;
gt;gt; gt; How would I do this?
gt;gt; gt;
gt;gt; gt; Thanks in advance.
gt;gt;
gt;gt;
gt;gt;