+ Reply to Thread
Results 1 to 3 of 3

=SUMPRODUCT formula is counting the blank cells as well as zero's

  1. #1
    JR
    Guest

    =SUMPRODUCT formula is counting the blank cells as well as zero's

    Hello,

    I am using the following to count a column of zero’s in a worksheet if the
    zero’s occur during specific times.

    =SUMPRODUCT(--(pathfinder_dump!G1:G10000>=TIME(1,0,0)),--(pathfinder_dump!G1:G10000<=TIME(1,29,59)),--(pathfinder_dump!I1:I10000=0))

    The trouble is that the formula is counting the blank cells as well. Can
    someone tell me how to modify so the formula only counts the zero’s?

    Thanks


  2. #2
    Sloth
    Guest

    RE: =SUMPRODUCT formula is counting the blank cells as well as zero's

    add this to your formula
    1-ISBLANK(C1:C6)

    =SUMPRODUCT(1-ISBLANK(C1:C6),--(pathfinder_dump!G1:G10000>=TIME(1,0,0)),--(pathfinder_dump!G1:G10000<=TIME(1,29,59)),--(pathfinder_dump!I1:I10000=0))

    "JR" wrote:

    > Hello,
    >
    > I am using the following to count a column of zero’s in a worksheet if the
    > zero’s occur during specific times.
    >
    > =SUMPRODUCT(--(pathfinder_dump!G1:G10000>=TIME(1,0,0)),--(pathfinder_dump!G1:G10000<=TIME(1,29,59)),--(pathfinder_dump!I1:I10000=0))
    >
    > The trouble is that the formula is counting the blank cells as well. Can
    > someone tell me how to modify so the formula only counts the zero’s?
    >
    > Thanks
    >


  3. #3
    Sloth
    Guest

    RE: =SUMPRODUCT formula is counting the blank cells as well as zero's

    sorry, I didn't change the range in my last post.

    =SUMPRODUCT(1-ISBLANK(pathfinder_dump!I1:I10000),--(pathfinder_dump!G1:G10000>=TIME(1,0,0)),--(pathfinder_dump!G1:G10000<=TIME(1,29,59)),--(pathfinder_dump!I1:I10000=0))

    "JR" wrote:

    > Hello,
    >
    > I am using the following to count a column of zero’s in a worksheet if the
    > zero’s occur during specific times.
    >
    > =SUMPRODUCT(--(pathfinder_dump!G1:G10000>=TIME(1,0,0)),--(pathfinder_dump!G1:G10000<=TIME(1,29,59)),--(pathfinder_dump!I1:I10000=0))
    >
    > The trouble is that the formula is counting the blank cells as well. Can
    > someone tell me how to modify so the formula only counts the zero’s?
    >
    > Thanks
    >


+ 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