+ Reply to Thread
Results 1 to 6 of 6

COUNTIFS with one of multiple criteria

  1. #1
    Forum Contributor
    Join Date
    08-04-2013
    Location
    Bucharest
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    301

    COUNTIFS with one of multiple criteria

    Is there a simple and elegant way to be able to choose between one of multiple criteria when using COUNTIFS? The formula should sound like this. I think it's pretty easy to understand, if not I'll put up an example.



    COUNTIFS(L:L, either "2-0" or 2-1", F:F,"CLAY")


    TY

  2. #2
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: COUNTIFS with one of multiple criteria

    Hi
    Try

    =SUM(COUNTIFS(L:L,{"2-0","2-1"}, F:F,"CLAY"))

    Remark: It's not good practice to use whole column references, it will make the workbook sluggish. Better to specify a row range.
    Last edited by lecxe; 03-23-2014 at 07:25 AM.

  3. #3
    Forum Contributor
    Join Date
    08-04-2013
    Location
    Bucharest
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    301

    Re: COUNTIFS with one of multiple criteria

    Thanks, I will specify a range. Can you shortly explain how SUM helps in this case?

  4. #4
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: COUNTIFS with one of multiple criteria

    Hi

    Quote Originally Posted by bibu View Post
    Can you shortly explain how SUM helps in this case?
    You are using an array to specify the 2 cases in the first condition. This means that CountIfs() will, in fact, calculate 2 values

    COUNTIFS(L:L,"2-0", F:F,"CLAY") (for ex., equal to 3)
    COUNTIFS(L:L,"2-1", F:F,"CLAY") (for ex., equal to 4)

    and will give you the result as an array with the 2 values, like {3,4}.
    To get the total result you have to add the values.

  5. #5
    Forum Contributor
    Join Date
    08-04-2013
    Location
    Bucharest
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    301

    Re: COUNTIFS with one of multiple criteria

    Oh, I see
    The COUNTIFS + COUNTIFS solution did cross my mind, but it didn't allow for the third criteria, the F:F,"CLAY", so after a couple of hours of "brainstorming" I gave up and asked for help :D

  6. #6
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: COUNTIFS with one of multiple criteria

    Quote Originally Posted by bibu View Post
    The COUNTIFS + COUNTIFS solution did cross my mind, ...
    Yes, you can also just add the 2 CountIfs() in my last post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Countifs with multiple criteria in single criteria range
    By MCP313 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2016, 03:51 AM
  2. [SOLVED] Countifs with multiple criteria and one criteria has a list of names
    By Beefy1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2014, 05:25 PM
  3. Countifs Multiple Criteria
    By Dohko in forum Excel General
    Replies: 11
    Last Post: 08-09-2011, 01:56 PM
  4. Replies: 0
    Last Post: 07-27-2011, 01:00 AM
  5. SUM COUNTIFS with multiple criteria
    By john_london in forum Excel General
    Replies: 4
    Last Post: 02-28-2011, 11:35 AM

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