+ Reply to Thread
Results 1 to 10 of 10

Countifs with multiple criteria in single criteria range

  1. #1
    Registered User
    Join Date
    09-21-2009
    Location
    Trenton, NJ
    MS-Off Ver
    Excel 2007
    Posts
    2

    Countifs with multiple criteria in single criteria range

    I am trying to use the countifs function to get a result. In my case there are multiple expressions that I want in a single criteria range. However, when I create the second expression the count goes to zero.

    EX: COUNTIFS(M1:M75, "C") yields count of 17

    COUNTIFS (M1:M75, "C", M1:M75, "A") yields count 0

    Is there a rule that you can not count multiples against the same criteria range?

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

    Re: Countifs with multiple criteria in single criteria range

    Correct, it is in effect an AND clause and a cell cannot be both C & A simultaneously hence 0.

    =SUM(COUNTIF(M1:M75,{"C","A"}))

  3. #3
    Registered User
    Join Date
    09-21-2009
    Location
    Trenton, NJ
    MS-Off Ver
    Excel 2007
    Posts
    2

    Thumbs up Re: Countifs with multiple criteria in single criteria range

    Many thanks, that did the trick!!

  4. #4
    Forum Contributor
    Join Date
    11-06-2012
    Posts
    139

    Re: Countifs with multiple criteria in single criteria range

    =SUM(COUNTIF(M1:M75,{"J21","K22"})

    Is there an option to let the red section shift to J22 and K23 if you go to the next cell?

  5. #5
    Forum Contributor
    Join Date
    11-06-2012
    Posts
    139

    Re: Countifs with multiple criteria in single criteria range

    =SUM(COUNTIF(M1:M75,{"J21","K22"})

    Is there an option to let the red section shift to J22 and K23 if you go to the next cell?

  6. #6
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Countifs with multiple criteria in single criteria range

    since your input parameter cells (red section in your example) are sharing neither row nor column, it is probably best to split the formula this way:

    =sum(countif($m$1:$m$75,$j21),countif($m$1:$m$75,$k22))

    when you drag this formula down, J21 and K22 will get incremented to J22 and K23.

    keep in mind, curly braces { } are for ARRAY CONSTANTS only (like in DonkeyOte's example above, only constant values such as "C" and "A" can reside within them); you cannot put cell addresses or formulae, or the like, within them...

    it is difficult to visualise without a sample file, but i hope you can figure the rest of the way out.

    991
    Last edited by icestationzbra; 12-12-2012 at 07:29 PM.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  7. #7
    Registered User
    Join Date
    10-08-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    5

    Re: Countifs with multiple criteria in single criteria range

    I have a similar problem.
    My 2 criterias are text values.
    How do I do it?

  8. #8
    Registered User
    Join Date
    06-10-2015
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    1

    Re: Countifs with multiple criteria in single criteria range

    This solved my problem partially =SUM(COUNTIF(M1:M75,{"C","A"})) . to this result I have to further apply this function to get desired result
    COUNTIF(M:M,"RESOLVED"). How do I combine both?

  9. #9
    Registered User
    Join Date
    12-01-2016
    Location
    Pune, Maharashtra, India
    MS-Off Ver
    Microsoft Office 2016
    Posts
    12

    Re: Countifs with multiple criteria in single criteria range

    Dear All,

    I have seen this thread "=SUM(COUNTIF(M1:M75,{"C","A"}))".
    In my worksheet I used this method, but what if some cell in crite range has the double value,
    means I have single criteria range with multiple criteria e.g. criteria "P" has value 1, criteria "PP" has value 2,
    criteria "P1" has value 0.125 then how to define it?

    Please help.

    Thanx in advance.

    Suhas

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,303

    Re: Countifs with multiple criteria in single criteria range

    SUHAS KARHADKAR welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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