• countif based on two columns (excel 2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » countif based on two columns (excel 2000)

    Author
    Topic
    WSjudithmc
    AskWoody Lounger
    May 6, 2005 at 2:07 am #419206

    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

    Reply | Quote
    Viewing 0 reply threads
    Author
    Replies
    • WSRudi
      AskWoody Lounger
      May 6, 2005 at 5:38 am #945826

      Try this function Judith

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

      See attachment!

      Reply | Quote
      • WSjudithmc
        AskWoody Lounger
        May 6, 2005 at 6:08 am #945828

        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,

        Reply | Quote
        • WSRudi
          AskWoody Lounger
          May 6, 2005 at 6:23 am #945830

          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!

          Reply | Quote
          • WSjudithmc
            AskWoody Lounger
            May 6, 2005 at 6:29 am #945832

            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

            Reply | Quote
    Viewing 0 reply threads
    Reply To: countif based on two columns (excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.