Cookies help us deliver our services. By using our services, you agree to our use of cookies.
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 two items for counting with multiple criteria.

Countif with multiple criteria in same column with formula

Countif with multiple criteria in multiple columns with formula


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 combine multiple worksheets-1

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Countif with multiple criteria in same column with formula



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-count-with-multiple-criteria-1

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

=COUNTIF(A2:A11,"KTE")+COUNTIF(A2:A11,"KTO"), (A2:A11 is the data range that you want to use, KTE and KTO are the criteria that you want to count), and then press Enter key to get the number of these two products. See screenshot:

doc-count-with-multiple-criteria-2

Notes:

1. If there are more than two criteria that you want to count in one column, just use COUNTIF(range, criteria) + COUNTIF(range, criteria) + COUNTIF(range, criteria)+…

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


arrow blue right bubble 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 or equal 200.

doc-count-with-multiple-criteria-3

Please type this formula into a desired cell: =COUNTIFS(A2:A11,"KTE",B2:B11,">=200") (A2:A11 and KTE are the first range and criterion you need, B2:B11 and >=200 is the second range and criterion you based on), and then press Enter key to get the number of KTE which you need.

doc-count-with-multiple-criteria-4

Notes:

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

2. Here is another formula also can help you: =SUMPRODUCT(--(A2:A11="KTE"),--(B2:B11>=200)), and press Enter key to return the result.


Related articles:

How to use countif to calculate the percentage in Excel?

How to countif a specific value across multiple worksheet?



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

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.
    Sbetarice · 1 years ago
    I have a spreadsheet where I need to count column v if it is equal to "EQ" and if columns j thru u are blank.
  • To post as a guest, your comment is unpublished.
    prasad · 1 years ago
    how to count a , b , c , d in excel . i want to count only a b d in excel not c .

    please tell formula
    • To post as a guest, your comment is unpublished.
      BASHIR AHMED · 1 years ago
      A
      B
      C
      D

      =COUNTIF(B2:B5,"A")+COUNTIF(B2:B5,"B")+COUNTIF(B2:B5,"D")
  • To post as a guest, your comment is unpublished.
    Shalini · 1 years ago
    SUMPRODUCT(COU NTIF(A:A,{C1;C1 &",*";"*,"&C1," *,"C1&",*"}))
    In this, C1 needs to be given in double quotes. Please check and verify
  • To post as a guest, your comment is unpublished.
    RANGYF · 1 years ago
    On formula =SUMPRODUCT(COUNTIF(range,{ "criteria";"criteria";"criteria";"criteria"…})), what if i want the content from some cell to form the criterias? Like this =SUMPRODUCT(COUNTIF(A:A,{C1;C1&",*";"*,"&C1,"*,"C1&",*"})), here i got syntax error. Seems it's illegal to use cell reference in {} array.
  • To post as a guest, your comment is unpublished.
    Jesse · 1 years ago
    On formula =COUNTIFS(A2:A11,"KTE",B2:B11,">=200") above. Why can you not note cell "A2" as the criteria instead of spelling out KTE. I know in this example KTE is short but not the case in my sheet.