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.
    cris · 1 days ago
    hi. hope i can get help with the setting up the correct data table and how to extract specific information from the table. here are the variables:

    we have multiple products under several different categories
    we have multiple sales rep assigned to specific territories
    i need to track their individual sales per product
    i also need to break down their sales per month, quarter, and on an annual basis (still per category, product and area)
    i need to compare the data of their actual sales versus their targets

    what's the correct data set, and the correct formula for it? thanks

    with these, i can then make a pivot table out of the data table.
  • To post as a guest, your comment is unpublished.
    MS · 6 days ago
    Can multiple arrays are possible 8n single countifs?

    Countifs(range,{criteria: criteria},range,{criteria: criteria}, range,{criteria: criteria})
  • To post as a guest, your comment is unpublished.
    Theo Bourgery · 10 days 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 · 1 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 · 1 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