+ Reply to Thread
Results 1 to 5 of 5

Countif for more than one condition

  1. #1
    Forum Contributor
    Join Date
    04-18-2009
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    267

    Smile Countif for more than one condition

    Hi All,

    I have a table with 2 columns - Gender & Age. Now, suppose I want to know the number of Males in the table with age greater than 14, how do I do that?

    Can a Countif formula be used for more than one condition or is there some other formula? I want to achieve it with the help of some formula. I think it should be easy but it is not striking me.

    Thanks in advance.

    Regards,
    Karan
    Last edited by karan; 09-18-2009 at 08:12 AM.

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

    Re: Countif for more than one condition

    Hello karan, you can use SUMPRODUCT function, something like this

    =SUMPRODUCT((A2:A100="Male")*(B2:B100>14))

    adjust ranges as required.....

  3. #3
    Forum Contributor
    Join Date
    04-18-2009
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    267

    Re: Countif for more than one condition

    Hi daddylonglegs,

    Thanks a lot for your help.

    I have seen numerous applications of the SUMPRODUCT function. How do I get to know all its advanced applications? I know there is a lot to explore in it. And, as far as COUNTIF is concerned, can it be used for more than one condition?

    Regards,
    Karan

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

    Re: Countif for more than one condition

    You can only use COUNTIF for one condition so SUMPRODUCT is the most common way to perform "multiconditional" counting and/or summing. Look here for Bob Phillips excellent SUMPRODUCT explanation/discussion

    Excel 2007 has COUNTIFS and SUMIFS function which allow more than one condition.

  5. #5
    Forum Contributor
    Join Date
    04-18-2009
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    267

    Thumbs up Re: Countif for more than one condition

    Thanks. That helped a lot..

    Regards,
    Karan

+ 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