+ Reply to Thread
Results 1 to 12 of 12

Countifs Multiple Criteria

  1. #1
    Registered User
    Join Date
    03-23-2007
    Posts
    91

    Countifs Multiple Criteria

    Hi,

    I have the following formula.
    =COUNTIFS(ccs_export!$D:$D,"abc",ccs_export!$AO:$AO,1)+COUNTIFS(ccs_export!$D:$D,"abc",ccs_export!$AS:$AS,1)

    Column D, Column AO, Column AS
    abc ,1,1
    abc ,1,1
    cdf ,0,1
    cdh ,1,0
    abc ,0,0

    Is this the correct formula for expressing the following

    IF Column D is "abc" and column AO is 1, or Column AS is 1, count 1.?

  2. #2
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Countifs Multiple Criteria

    Hi,

    Is this the correct formula for expressing the following

    IF Column D is "abc" and column AO is 1, or Column AS is 1, count 1.?
    Yes, but a potential issue with that formula is that if both AO and AS equal 1, then 2 will be counted (one for each). Is that what you want?
    Hope that helps,

    Colin

    RAD Excel Blog

  3. #3
    Registered User
    Join Date
    03-23-2007
    Posts
    91

    Re: Countifs Multiple Criteria

    No that is not what I want.
    If one of them is 1. Then count only 1.
    In fact I'm using 15 countifs, I just entered 2 as an example.
    Basically if one of those columns has 1, then count 1.
    How do I achieve this?

  4. #4
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Countifs Multiple Criteria

    One way would be to add a helper column which returns 1 for each row if any of the relevant values equal 1. Then a simple SUM formula can sum up the 1s and 0s in that helper column. The helper column formula would be something like this, copied down the column:
    Please Login or Register  to view this content.
    Another option would be to adapt your COUNTIFS formula like this:
    Please Login or Register  to view this content.
    This option would not be practical for the potential combinations of 15 COUNTIFS() functions.

    Another option would be to use SUMPRODUCT.
    Please Login or Register  to view this content.
    If you go for the SUMPRODUCT option, try not to use whole column references because it will slow your workbook down.
    Last edited by Colin Legg; 08-09-2011 at 12:06 PM.

  5. #5
    Registered User
    Join Date
    03-23-2007
    Posts
    91

    Re: Countifs Multiple Criteria

    So basically since I can't add an extra column, Sumproduct would solve the problem?

  6. #6
    Registered User
    Join Date
    03-23-2007
    Posts
    91

    Re: Countifs Multiple Criteria

    The only problem with this is that SumProduct is very slow.

  7. #7
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Countifs Multiple Criteria

    Yes, here's another SUMPRODUCT example to show you how to expand the formula to 4 columns.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    03-23-2007
    Posts
    91

    Re: Countifs Multiple Criteria

    Now lets say that in addition to Column D, i also need to Add Column E and EXCLUDE everything in that column that says XYZ and CDE.

  9. #9
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Countifs Multiple Criteria

    Here's one way of several:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-23-2007
    Posts
    91

    Re: Countifs Multiple Criteria

    To exclude I assume I would use <>?
    Also the =0 is important?

  11. #11
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Countifs Multiple Criteria

    Hi,
    To exclude I assume I would use <>?
    No, what I posted excludes them.

    Also the =0 is important?
    Yes, it's important. Instead of using a whole column, let's understand that part of the formula by using a single cell, say A1.

    Starting with this:
    ((A1="XYZ")+(A1="CDE"))

    If A1 contains either XYZ or CDE then that formula will return 1. If A1 contains neither XYZ nor CDE then the formula returns 0. So, to exclude XYZ and CDE, we equate that result to 0.

    ((A1="XYZ")+(A1="CDE"))=0


    That's what I did in the formula, except for a whole column:

    ((ccs_export!$E2:$E100="XYZ")+(ccs_export!$E2:$E100="CDE"))=0

  12. #12
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Countifs Multiple Criteria

    See this site for a great explaination on the SUMPRODUCT function.

    http://xldynamic.com/source/xld.SUMPRODUCT.html

+ 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