+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS function not working

  1. #1
    Registered User
    Join Date
    08-25-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    1

    COUNTIFS function not working

    Why won't this work? Trying to count cells that have "TEST" in column B and if it has a "Y" in the range from columns D to G....

    =COUNTIFS(B2:B200,"TEST", D2:G200, "Y")

    I need the second criteria to be across a range of columns where it equals "Y" and I get error this way. If I only look in one column (e.g. D) it works fine and returns the count.

    Hoping someone can help. Thanks...

  2. #2
    Registered User
    Join Date
    07-06-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2007,2010
    Posts
    27

    Re: COUNTIFS function not working

    I think you need to separate each column and assign the corresponding values. I think that's the reason why the function "countifs" has many criteria so you could specify your parameters.

    I'm not sure if this will help. I'm not an expert in excel but I hope this could help:

    =COUNTIFS(B2:B200,"test",D2:D200,"y",E2:E200,"y",F2:F200,"y",G2:G200,"y")

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: COUNTIFS function not working

    Don't the criteria ranges all need to be the same size?

    Your first range is 199 rows by 1 column, the second range 199 rows by 4 columns.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: COUNTIFS function not working

    COUNTIFS is by definition an AND based test - so knightcloud's approach will return 1 wherein B is Test and each value in D:G is "Y".

    If you wish to conduct an OR test - which I think you do - then one approach might be:

    Please Login or Register  to view this content.
    ORs in Arrays/SUMPRODUCTs are generally conducted by means of addition, ie:

    Please Login or Register  to view this content.
    the use of SIGN is to prevent double counting - ie should range2 and range4 contain x then count as 1 rather than 2
    (you could use >0 test rather than SIGN if preferred)
    Last edited by DonkeyOte; 08-26-2010 at 02:29 AM.

+ 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