+ Reply to Thread
Results 1 to 6 of 6

Countifs function with multiple criteria in same column

  1. #1
    Registered User
    Join Date
    07-03-2012
    Location
    md
    MS-Off Ver
    Excel 2010
    Posts
    37

    Countifs function with multiple criteria in same column

    Hello,
    I have spreadsheet Containing data as shown below.
    Country Government Data Grade Type
    India Democracy H B L1
    China Communist H A D2
    USA Democracy M A D2
    UK Democracy L B L2
    Canada Democracy M C D1
    Russia Communist M A D3

    I want to count the Countries belonging the Democratic group having data type of H or M and Grade A
    I want to use Countifs (government,democracy, Data, or(H,M), Grade,A) as is but or it looks like i cannot use the OR function as shown in the formula written. Can u tell me if one can use or statement within a countif fnuction

    Appreaciate the help.

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Countifs function with multiple criteria in same column

    maybe like this

    Countifs (government,democracy, Data, {"H","M"}, Grade,A)
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    07-03-2012
    Location
    md
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Countifs function with multiple criteria in same column

    Does not work. Thanks for the suggestion. the only way I have found out is to use countifs twice ie (Counitfs for H + Countifs for M) However, it becomes cumbersome once u have multiple columns with more than one criteria.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Countifs function with multiple criteria in same column

    Vlady's formula works if you add a SUM function like this

    =SUM(COUNTIFS(government,"Democracy",Data,{"H","M"},Grade,"A"))

    If you have another column with multiple criteria e.g. grade = "A" or "B" or "C" then you need to use ; separator for that second one only, i.e.

    =SUM(COUNTIFS(government,"Democracy",Data,{"H","M"},Grade,{"A";"B";"C"}))

    Any more than 2 multi-criteria ranges and you probably need SUMPRODUCT instead....
    Last edited by daddylonglegs; 08-28-2012 at 09:05 AM.
    Audere est facere

  5. #5
    Registered User
    Join Date
    07-03-2012
    Location
    md
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Countifs function with multiple criteria in same column

    Yes, It does. Thanks a lot to vlady and to you. I am surprised that the function does not work with or function for some reason.

  6. #6
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Countifs function with multiple criteria in same column

    How about:

    =SUM((Government="Democracy")*(Data={"H","M"}))

    Confirmed with Ctrl-Shift-Enter

+ 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