+ Reply to Thread
Results 1 to 6 of 6

Countif to return partial result?

  1. #1
    Forum Contributor
    Join Date
    03-02-2010
    Location
    Northumberland, England
    MS-Off Ver
    Excel 2010
    Posts
    140

    Countif to return partial result?

    Hi Im a bit stuck

    Im using countifs to return values based on 3 set criteria. The criteria are chosen using vlookups

    Is it possible to return partial search results based only on 2 criteria?

    Ive uploaded a mock up to demonstrate.

    Ive selected Sat as Criteria 1, and Aug For Criteria 2, but nothing yet for criteria 3. As predicted the COUNT is zero because all 3 criteria have not been met..

    Id like it to be able to return partial results, Is that possible

    There for the COUNT would be 4 as Sat, and Aug appear 4 times.#

    HELP much appreciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,217

    Re: Countif to return partial result?

    How about:

    HTML Code: 
    =COUNTIF(C4:C19,J3)+COUNTIF(D4:D19,J4)+COUNTIF(E4:E19,J5)
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,217

    Re: Countif to return partial result?

    sorry, misunderstood the issue.
    Let me think of something else.

  4. #4
    Forum Contributor
    Join Date
    03-02-2010
    Location
    Northumberland, England
    MS-Off Ver
    Excel 2010
    Posts
    140

    Re: Countif to return partial result?

    Thanks modytrane

  5. #5
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,217

    Re: Countif to return partial result?

    If Year is the only one that could be blank then you can use the following:

    HTML Code: 
    =IF(J5<>0,COUNTIFS(C4:C20,J3,D4:D20,J4,E4:E20,J5),COUNTIFS(C4:C20,J3,D4:D20,J4))
    With the situation described in your post, this will give you result of 4.

    If it is possible to be missing any one of those three [Day, Month or Year] then you may have to build in IF conditions for all three possibilities.
    Something like:

    HTML Code: 
    =IF(J5="",COUNTIFS(C4:C20,J3,D4:D20,J4),IF(J4="",COUNTIFS(C4:C20,J3,E4:E20,J5),IF(J3="",COUNTIFS(D4:D20,J4,E4:E20,J5),COUNTIFS(C4:C20,J3,D4:D20,J4,E4:E20,J5))))

  6. #6
    Valued Forum Contributor
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2007
    Posts
    1,217

    Re: Countif to return partial result?

    You also have to clean up few other things.
    Your day column has Tue and Tues.
    Your data validation list has Jan.Feb,Mar,Apr.........
    You need to change . to , after Jan.

+ 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