+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT - Blank Cells

  1. #1
    Peggy Sue
    Guest

    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. #2
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    Quote 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
    Paul

  3. #3
    Bob Phillips
    Guest

    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 [email protected]> wrote in message
    news:[email protected]...
    > 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. #4
    Roger Govier
    Guest

    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
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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