SUMPRODUCT - Blank Cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
It doesn't count blanks as zero in my test.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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 said:
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
Click to expand...

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
 
Back
Top