# sumproduct with 0/blank cells

1. ## 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?

2. ## Re: sumproduct with 0/blank cells

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

3. ## Re: sumproduct with 0/blank cells

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

4. ## Re: sumproduct with 0/blank cells

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

>
>
>

5. ## Re: sumproduct with 0/blank cells

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

>>
>>
>>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1