# SUMPRODUCT - Blank Cells

1. ## SUMPRODUCT - Blank Cells

Any idea of a way to use the last portoin of this formula to count zeros
instead of treating all blank cells as zeros? I am trying to bring back a
combination that would consist of programs that have an OPEN status and have
a type of NPD at a Max Gate Status of 0 = 2. My data combination is below.

=SUMPRODUCT(--('System Extract'!\$B\$2:\$B\$1000="OPEN"),--('System
Extract'!\$C\$2:\$C\$1000="NPD"),--('System Extract'!\$E\$2:\$E\$1000=0))

Status Type Max Gate Status
OPEN NPD 0
OPEN NPE
OPEN NPE 0
CANCL NPD 0
OPEN PLE
OPEN NPD 0
OPEN PLE 1
OPEN NPE 1
CLOSE PLE 2
OPEN NPD 2
OPEN NPD 2
OPEN NPD 2
OPEN PLE 4

2. Originally Posted by Peggy Sue
Any idea of a way to use the last portoin of this formula to count zeros
instead of treating all blank cells as zeros? I am trying to bring back a
combination that would consist of programs that have an OPEN status and have
a type of NPD at a Max Gate Status of 0 = 2. My data combination is below.

=SUMPRODUCT(--('System Extract'!\$B\$2:\$B\$1000="OPEN"),--('System
Extract'!\$C\$2:\$C\$1000="NPD"),--('System Extract'!\$E\$2:\$E\$1000=0))

Status Type Max Gate Status
OPEN NPD 0
OPEN NPE
OPEN NPE 0
CANCL NPD 0
OPEN PLE
OPEN NPD 0
OPEN PLE 1
OPEN NPE 1
CLOSE PLE 2
OPEN NPD 2
OPEN NPD 2
OPEN NPD 2
OPEN PLE 4
Hi Peggy Sue

You could try this ---->

=SUM((B2:B1000="OPEN")*(C2:C1000="NPD")*(E2:E1000=0))

enterred as an array with CTRL/SHIFT/ENTER

3. ## Re: SUMPRODUCT - Blank Cells

It doesn't count blanks as zero in my test.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Peggy Sue" <Peggy Sue@discussions.microsoft.com> wrote in message
news:E3BE8954-D2DA-4C7B-89E8-F505EC7E9571@microsoft.com...
> Any idea of a way to use the last portoin of this formula to count zeros
> instead of treating all blank cells as zeros? I am trying to bring back a
> combination that would consist of programs that have an OPEN status and

have
> a type of NPD at a Max Gate Status of 0 = 2. My data combination is

below.
>
> =SUMPRODUCT(--('System Extract'!\$B\$2:\$B\$1000="OPEN"),--('System
> Extract'!\$C\$2:\$C\$1000="NPD"),--('System Extract'!\$E\$2:\$E\$1000=0))
>
> Status Type Max Gate Status
> OPEN NPD 0
> OPEN NPE
> OPEN NPE 0
> CANCL NPD 0
> OPEN PLE
> OPEN NPD 0
> OPEN PLE 1
> OPEN NPE 1
> CLOSE PLE 2
> OPEN NPD 2
> OPEN NPD 2
> OPEN NPD 2
> OPEN PLE 4
>

4. ## Re: SUMPRODUCT - Blank Cells

Hi

One way, add a further test for cells in column E <> ""
=SUMPRODUCT(--('System Extract'!\$B\$2:\$B\$1000="OPEN"),--('System
Extract'!\$C\$2:\$C\$1000="NPD"),--('System Extract'!\$E\$2:\$E\$1000=0),--('System
Extract'!\$E\$2:\$E\$1000<>""))

Regards

Roger Govier

Peggy Sue wrote:
> Any idea of a way to use the last portoin of this formula to count zeros
> instead of treating all blank cells as zeros? I am trying to bring back a
> combination that would consist of programs that have an OPEN status and have
> a type of NPD at a Max Gate Status of 0 = 2. My data combination is below.
>
> =SUMPRODUCT(--('System Extract'!\$B\$2:\$B\$1000="OPEN"),--('System
> Extract'!\$C\$2:\$C\$1000="NPD"),--('System Extract'!\$E\$2:\$E\$1000=0))
>
> Status Type Max Gate Status
> OPEN NPD 0
> OPEN NPE
> OPEN NPE 0
> CANCL NPD 0
> OPEN PLE
> OPEN NPD 0
> OPEN PLE 1
> OPEN NPE 1
> CLOSE PLE 2
> OPEN NPD 2
> OPEN NPD 2
> OPEN NPD 2
> OPEN PLE 4
>

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