+ Reply to Thread
Results 1 to 6 of 6

Count nonblank cells with multiple criteria

  1. #1
    Daniel
    Guest

    Count nonblank cells with multiple criteria

    I have a fairly large spread sheet that I usually filter or us a pivot table
    to get what I need and then manually input into another workbook. What I
    need is to count the nonblank cells in column G (which consists of times in
    hh:mm:ss format) based on column A = KM06 and column C = 1 (both general
    format).

    I have tried using the sumproduct, sumif, if, and count.

    Thank You in advance,
    --
    Daniel

  2. #2
    Bob Phillips
    Guest

    Re: Count nonblank cells with multiple criteria

    =SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20))))

    --

    HTH

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


    "Daniel" <Daniel@discussions.microsoft.com> wrote in message
    news:4C24688A-A5FE-4915-B8DB-C40F22D6DF1B@microsoft.com...
    > I have a fairly large spread sheet that I usually filter or us a pivot

    table
    > to get what I need and then manually input into another workbook. What I
    > need is to count the nonblank cells in column G (which consists of times

    in
    > hh:mm:ss format) based on column A = KM06 and column C = 1 (both general
    > format).
    >
    > I have tried using the sumproduct, sumif, if, and count.
    >
    > Thank You in advance,
    > --
    > Daniel




  3. #3
    Daniel
    Guest

    Re: Count nonblank cells with multiple criteria

    Thank you Bob, that worked great!! I have one more question, that I didn't
    think of earlier. What if I wanted the criteria for column C to be 1 or 2
    instead of just 1?

    Thank You Again,
    --
    Daniel


    "Bob Phillips" wrote:

    > =SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20))))
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Daniel" <Daniel@discussions.microsoft.com> wrote in message
    > news:4C24688A-A5FE-4915-B8DB-C40F22D6DF1B@microsoft.com...
    > > I have a fairly large spread sheet that I usually filter or us a pivot

    > table
    > > to get what I need and then manually input into another workbook. What I
    > > need is to count the nonblank cells in column G (which consists of times

    > in
    > > hh:mm:ss format) based on column A = KM06 and column C = 1 (both general
    > > format).
    > >
    > > I have tried using the sumproduct, sumif, if, and count.
    > >
    > > Thank You in advance,
    > > --
    > > Daniel

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: Count nonblank cells with multiple criteria

    Yeah, that is possible too

    =SUMPRODUCT((A2:A20="KM06")*(C2:C20={1,2})*(NOT(ISBLANK(G2:G20))))

    --

    HTH

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


    "Daniel" <Daniel@discussions.microsoft.com> wrote in message
    news:C3C428AF-618B-4CAB-AFCC-31B052746364@microsoft.com...
    > Thank you Bob, that worked great!! I have one more question, that I

    didn't
    > think of earlier. What if I wanted the criteria for column C to be 1 or 2
    > instead of just 1?
    >
    > Thank You Again,
    > --
    > Daniel
    >
    >
    > "Bob Phillips" wrote:
    >
    > > =SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20))))
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Daniel" <Daniel@discussions.microsoft.com> wrote in message
    > > news:4C24688A-A5FE-4915-B8DB-C40F22D6DF1B@microsoft.com...
    > > > I have a fairly large spread sheet that I usually filter or us a pivot

    > > table
    > > > to get what I need and then manually input into another workbook.

    What I
    > > > need is to count the nonblank cells in column G (which consists of

    times
    > > in
    > > > hh:mm:ss format) based on column A = KM06 and column C = 1 (both

    general
    > > > format).
    > > >
    > > > I have tried using the sumproduct, sumif, if, and count.
    > > >
    > > > Thank You in advance,
    > > > --
    > > > Daniel

    > >
    > >
    > >




  5. #5
    Daniel
    Guest

    Re: Count nonblank cells with multiple criteria

    Thanks Again!! I don't think I would've gotten that one on my own!!
    --
    Daniel


    "Bob Phillips" wrote:

    > Yeah, that is possible too
    >
    > =SUMPRODUCT((A2:A20="KM06")*(C2:C20={1,2})*(NOT(ISBLANK(G2:G20))))
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Daniel" <Daniel@discussions.microsoft.com> wrote in message
    > news:C3C428AF-618B-4CAB-AFCC-31B052746364@microsoft.com...
    > > Thank you Bob, that worked great!! I have one more question, that I

    > didn't
    > > think of earlier. What if I wanted the criteria for column C to be 1 or 2
    > > instead of just 1?
    > >
    > > Thank You Again,
    > > --
    > > Daniel
    > >
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > =SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20))))
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Daniel" <Daniel@discussions.microsoft.com> wrote in message
    > > > news:4C24688A-A5FE-4915-B8DB-C40F22D6DF1B@microsoft.com...
    > > > > I have a fairly large spread sheet that I usually filter or us a pivot
    > > > table
    > > > > to get what I need and then manually input into another workbook.

    > What I
    > > > > need is to count the nonblank cells in column G (which consists of

    > times
    > > > in
    > > > > hh:mm:ss format) based on column A = KM06 and column C = 1 (both

    > general
    > > > > format).
    > > > >
    > > > > I have tried using the sumproduct, sumif, if, and count.
    > > > >
    > > > > Thank You in advance,
    > > > > --
    > > > > Daniel
    > > >
    > > >
    > > >

    >
    >
    >


  6. #6
    Bob Phillips
    Guest

    Re: Count nonblank cells with multiple criteria

    No, it is not an intuitive leap from the previous one :-))

    Bob


    "Daniel" <Daniel@discussions.microsoft.com> wrote in message
    news:E679C647-2AC6-4EF3-9B80-40005B55876D@microsoft.com...
    > Thanks Again!! I don't think I would've gotten that one on my own!!
    > --
    > Daniel
    >
    >
    > "Bob Phillips" wrote:
    >
    > > Yeah, that is possible too
    > >
    > > =SUMPRODUCT((A2:A20="KM06")*(C2:C20={1,2})*(NOT(ISBLANK(G2:G20))))
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Daniel" <Daniel@discussions.microsoft.com> wrote in message
    > > news:C3C428AF-618B-4CAB-AFCC-31B052746364@microsoft.com...
    > > > Thank you Bob, that worked great!! I have one more question, that I

    > > didn't
    > > > think of earlier. What if I wanted the criteria for column C to be 1

    or 2
    > > > instead of just 1?
    > > >
    > > > Thank You Again,
    > > > --
    > > > Daniel
    > > >
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > =SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20))))
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Daniel" <Daniel@discussions.microsoft.com> wrote in message
    > > > > news:4C24688A-A5FE-4915-B8DB-C40F22D6DF1B@microsoft.com...
    > > > > > I have a fairly large spread sheet that I usually filter or us a

    pivot
    > > > > table
    > > > > > to get what I need and then manually input into another workbook.

    > > What I
    > > > > > need is to count the nonblank cells in column G (which consists of

    > > times
    > > > > in
    > > > > > hh:mm:ss format) based on column A = KM06 and column C = 1 (both

    > > general
    > > > > > format).
    > > > > >
    > > > > > I have tried using the sumproduct, sumif, if, and count.
    > > > > >
    > > > > > Thank You in advance,
    > > > > > --
    > > > > > Daniel
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




+ 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