SUMPRODUCT - Blank Cells

Peggy Sue · 11-01-2005, 04:09 PM

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

**Paul Sheppard** · 11-02-2005, 07:48 AM

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

Bob Phillips · 11-02-2005, 06:17 PM

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
>

Roger Govier · 11-02-2005, 06:17 PM

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
>