Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Cherrybrook, New South Wales, Australia
    Posts
    116
    Thanks
    6
    Thanked 0 Times in 0 Posts

    countif based on two columns (excel 2000)

    Hi,
    In my spreadsheet, I'm trying to calculate the percentage of jobs in a certain category that are not completed.
    I have columns for category and for signoff.
    I've used Countif to count the number of jobs in the category but don't know how to count "jobs where category =wp03 and signoff=blank"
    Could someone please point me to the function to use?
    Thanks in advance,
    Judith

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: countif based on two columns (excel 2000)

    Try this function Judith

    {=SUM(IF(B2:B6="wp03",IF(ISBLANK(C2:C6),1,0),0))} : based on attached sample!

    See attachment!
    Regards,
    Rudi

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Cherrybrook, New South Wales, Australia
    Posts
    116
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: countif based on two columns (excel 2000)

    Thanks Rudi,
    The function is brilliant.
    I've even expanded it to match 3 criteria, one of which is "not blank".
    I didn't know the cntl-shift-enter trick for arrays.

    Many thanks,

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: countif based on two columns (excel 2000)

    I've been playing around with some other examples and came up with:

    {=SUM((B2:B6="wp03")*ISBLANK(C2:C6))} also arrayed using CTRL+SHIFT+ENTER
    and
    =SUMPRODUCT((B2:B6="wp03")*ISBLANK(C2:C6)). There is no need to array the function here! Simplifies the matter!
    Regards,
    Rudi

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Cherrybrook, New South Wales, Australia
    Posts
    116
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: countif based on two columns (excel 2000)

    I like the idea of the product. I'll be leaving this spreadsheet for my successor and the product method seems more robust for when he or she wants to fiddle with it.
    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •