sumproduct with 0/blank cells

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

=SUMPRODUCT((report!$Y$4:$Y$400<60)*(report!$Y$4:$Y$400>-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$400<60)*(report!$Y$4:$Y$400>-60))

Biff

"Matt" <Matt@discussions.microsoft.com> wrote in message
news:07A50746-F299-40B6-9453-7835F4AB1402@microsoft.com...
>I am using sumproduct to count a total number of cases. The formula I have
> been using is:
>
> =SUMPRODUCT((report!$Y$4:$Y$400<60)*(report!$Y$4:$Y$400>-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.

=SUMPRODUCT(--(report!$Y$4:$Y$400>-60),--(report!$Y$4:$Y$400<60),--(report!$
Y$4:$Y$400<>""))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Matt" <Matt@discussions.microsoft.com> wrote in message
news:07A50746-F299-40B6-9453-7835F4AB1402@microsoft.com...
> I am using sumproduct to count a total number of cases. The formula I
have
> been using is:
>
> =SUMPRODUCT((report!$Y$4:$Y$400<60)*(report!$Y$4:$Y$400>-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.

Thanks! This worked like I wanted it to.

I really appreciate it.

"Biff" wrote:

> Hi!
>
> Try this:
>
> =SUMPRODUCT((ISNUMBER(report!$Y$4:$Y$400))*(report!$Y$4:$Y$400<60)*(report!$Y$4:$Y$400>-60))
>
> Biff
>
> "Matt" <Matt@discussions.microsoft.com> wrote in message
> news:07A50746-F299-40B6-9453-7835F4AB1402@microsoft.com...
> >I am using sumproduct to count a total number of cases. The formula I have
> > been using is:
> >
> > =SUMPRODUCT((report!$Y$4:$Y$400<60)*(report!$Y$4:$Y$400>-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.
>
>
>

You're welcome. Thanks for the feedback!

Biff

"Matt" <Matt@discussions.microsoft.com> wrote in message
news:A4BA41B3-EFA8-4637-8F16-02EF55E7C373@microsoft.com...
> Thanks! This worked like I wanted it to.
>
> I really appreciate it.
>
> "Biff" wrote:
>
>> Hi!
>>
>> Try this:
>>
>> =SUMPRODUCT((ISNUMBER(report!$Y$4:$Y$400))*(report!$Y$4:$Y$400<60)*(report!$Y$4:$Y$400>-60))
>>
>> Biff
>>
>> "Matt" <Matt@discussions.microsoft.com> wrote in message
>> news:07A50746-F299-40B6-9453-7835F4AB1402@microsoft.com...
>> >I am using sumproduct to count a total number of cases. The formula I
>> >have
>> > been using is:
>> >
>> > =SUMPRODUCT((report!$Y$4:$Y$400<60)*(report!$Y$4:$Y$400>-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.
>>
>>
>>