Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to countif with multiple criteria in Excel?

In Excel, COUNTIF function may help us to calculate the number of a certain value in a list. But sometimes, we need to use multiple criteria for counting, this will be more complex, today, I will talk about some items for counting with multiple criteria.

Method1: Countif with multiple criteria in same column

Method2: Countif with multiple criteria in multiple columns

doc countif multiple criteria 9
 

Combine multiple worksheets, workbooks and csv files into one worksheet / workbook:

In your daily work, to combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge and headachy work. But, if you have Kutools for Excel, with its powerful utility – Combine, you can quickly combine multiple worksheets, workbooks into one worksheet or workbook.

doc countif multiple criteria 12

Download and free trial 60-day

 

Countif with multiple criteria in same column


office tab pic

Layoff season is coming, still work slowly?
-- Office Tab boosts your pace, saves 50% work time!

  •  Amazing! The operation of multiple documents is even more relaxing and convenient than single document;
  •  Compared with other web browsers, the interface of Office Tab is more powerful and aesthetic;
  •  Reduce thousands of tedious mouse clicks, say goodbye to cervical spondylosis and mouse hand;
  •  Be chosen by 90,000 elites and 300+ well-known companies!
Full feature, Free Trial 30-day          Read More             Download Now!
 

Count cells with multiple criteria based on text value

For example, I have the following data which contains some products, and now I need to count the number of KTE and KTO which are populated in the same column, see screenshot:

doc countif multiple criteria 1

To get the number of KTE and KTO, please enter this formula:

=COUNTIF($A$2:$A$15,"KTE")+COUNTIF($A$2:$A$15,"KTO"),  and then press Enter key to get the number of these two products. See screenshot:

doc countif multiple criteria 2

Notes:

1. In the above formula: A2:A15 is the data range that you want to use, KTE and KTO are the criteria that you want to count.

2. If there are more than two criteria that you want to count in one column, just use =COUNTIF(range1, criteria1) + COUNTIF(range2, criteria2) + COUNTIF(range3, criteria3)+…

Tips: Another compact formula also can help you to solve this problem: =SUMPRODUCT(COUNTIF($A$2:$A$15,{"KTE";"KTO"})), and then press Enter key to get the result. And you can add the criteria just as =SUMPRODUCT(COUNTIF(range,{ "criteria1";"criteria2";"criteria3";"criteria4"…})).

doc countif multiple criteria 3


Count cells with multiple criteria between two values

If you need to count the number of cells that the value is between two given numbers, how to solve this job in Excel?

Take the below screenshot as example, I want to get the result of the number that is between 200 and 500. Please do with these formulas:

doc countif multiple criteria 4

Enter this formula into a blank cell where you want to locate the result: =COUNTIF($B$2:$B$15,">200")-COUNTIF($B$2:$B$15,">500"),and then press Enter key to get the result as you need, see screenshot:

doc countif multiple criteria 5

Note: In the above formula:

  • B2:B15 is the cell range that you want to use;
  • >200 and >500 are the criteria you want to count the cells;
  • the whole formula means, to find the number of cells that have a value greater than 200 and then subtract the count of cells with a value greater than 500.

Tips: You can also apply the COUNTIFS function to deal with this task, please type this formula: =COUNTIFS($B$2:$B$15,">200",$B$2:$B$15,"<500").And you can add the criteria just as =COUNTIFS(range1,"criteria1",range2,"criteria2",range3,"criteria3",...).

doc countif multiple criteria 6


Count cells with multiple criteria between two dates

To count the cells based on date range, the COUNTIF and COUNTIFS functions can do you a favor as well.

For example, I want to count the cell numbers that the date is between 11/30/2018 and 2/20/2019 in a column, please do as this:

Enter this formula: =COUNTIF($B$2:$B$15, ">11/30/2018") - COUNTIF($B$2:$B$15, ">2/20/2019") into a blank cell to output the result, and then press Enter key to get the count number, see screenshot:

doc countif multiple criteria 7

Note: In the above formula:

  • B2:B15 is the cell range that you want to use;
  • >11/30/2018 and >2/20/2019 are the date criteria you want to count the cells;
  • the whole formula means, to find the number of cells that date greater than 11/30/2018 and then subtract the count of cells with a date greater than 2/20/2019.

Tips: You can also apply the COUNTIFS function to deal with this task, please type this formula: =COUNTIFS($B$2:$B$15, ">11/30/2018", $B$2:$B$15, "<2/20/2019").

doc countif multiple criteria 8



Countif with multiple criteria in multiple columns with formula

If there are multiple criteria in multiple columns, such as following screenshot shown, and I want to get the number of KTE whose order is greater than 200 and the name is Ruby.

doc countif multiple criteria 9

Please type this formula into a desired cell: =COUNTIFS($A$2:$A$15,"KTE",$B$2:$B$15,">300",$C$2:$C$15,"Ruby"), and then press Enter key to get the number of KTE which you need.

doc countif multiple criteria 10

Notes:

1. A2:A15 and KTE are the first range and criterion you need, B2:B15 and >300 are the second range and criterion you need, and the C2:C15 and Ruby are the third range and criterion you based on.

2. If there are more criteria you need based on, you just need to add the range and criteria within the formula, such as: =COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3, range4, criteria4,… )

Tips: Here is another formula also can help you: =SUMPRODUCT(--($A$2:$A$15="KTE"),--($B$2:$B$15>300),--($C$2:$C$15="Ruby")), and press Enter key to return the result.

doc countif multiple criteria 11


Related articles:

How to use countif to calculate the percentage in Excel?

How to countif a specific value across multiple worksheet?


Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Theo Bourgery · 6 months ago
    My column A contains a set of different categories. My column B contains dates as "1 October 2018", but my filter is by year ("2018").
    Both [ =SUMPRODUCT(--(A:A="Category x"),--(B:B="2018") ] and [ =COUNTIFS(A:A,"Category x",B:B,"2018) ] give me a result of zero, which is evidently incorrect. Could there by something wrong with my date filter?

    Thanks!
  • To post as a guest, your comment is unpublished.
    David Uhrlaff · 7 months ago
    I am not able to upload the image of my data. neither .png file nor .bmp file upload. Any advice anyone?
    thanks
    Dave U.
  • To post as a guest, your comment is unpublished.
    David Uhrlaff · 7 months ago
    I'm showing 3 tables. The middle table shows lab data. In my example, I want to count any platelet values (PLAT) that have a supporting event in the left table, with matching dates. My formula in Column M looks like this:

    SUM(COUNTIFS(C:C, J13, D:D, {"Thrombocytopenia","Platelet count decreased"}, E:E, "<="&EDATE(L13, 0), F:F, ">="&EDATE(L13, 0)) + COUNTIFS(C:C, J13, D:D, {"Thrombocytopenia","Platelet count decreased"}, E:E, "<="&EDATE(L13, 0), G:G, "AFTER"))

    This formula works; however, I must HARDCODE the values "Thrombocytopenia" and "Platelet count decreased". I would like it to work dynamically where it references Column Q, or perhaps cells Q10 and Q11, where it uses that text based on the matching lab name (e.g., PLAT). In essence, I'm looking for a nested OR statement that behaves dynamically within the middle of a COUNTIFS statement. Tricky..... maybe I need to learn how to use --SUMPRODUCT. Notice the NEUT lab test in the far right table which has 3 "events" that would be acceptable to find in the leftmost table... I would want them to be counted, eventually when I find a good formula.

    thanks - Dave U
  • To post as a guest, your comment is unpublished.
    Susan · 8 months ago
    I have another request if possible, I am looking for a formula that will give me staff holiday cover, there are 5 people and each person has to cover at least one day over Christmas and New Year, each person has to give me what holiday entitlement they have left for the year so that I can calculate the cover.
  • To post as a guest, your comment is unpublished.
    Susan · 8 months ago
    Just wondering if you can help, I need a formula to decide a Pass or Fail as the result. The following data is in 6 columns with either a yes or no in them, if the results are all “yes” then this is a pass, if any one column has a “no” then this is a fail. I have tried various formulas with “IF” “AND” “OR” but nothing gives me what I am looking for. Thank you in advance.
    • To post as a guest, your comment is unpublished.
      skyyang · 8 months ago
      Hello, Susan,
      To solve your problem, you can apply this formula:
      =IF((COUNTIF(B2:G2,"no")),"Fail","Pass")
      Change the cell references to your own.
      Hope it can help you!