Back to Blog

COUNTIF Excel Multiple Criteria – Formula Syntax & Example

The Excel COUNTIF function works well for counting values by one criterion. At the same time, you can also specify multiple criteria in your formula for a more thorough result. Read on to discover the details of Excel COUNTIF with multiple criteria.

Excel COUNTIF multiple criteria formula syntax 

=COUNTIF(cell-range,{criterion1,criterion2,criterion3…})

Example of how to COUNTIF in Excel for multiple criteria 

To demonstrate an example of Excel COUNTIF multiple criteria, let’s use the query we imported from BigQuery to Excel using Coupler.io, a solution for automatic data transfer from different apps and sources to Excel. 

Check out the available Excel integrations, such as Harvest or Google Sheets, and try out Coupler.io for your project.

Excel COUNTIF multiple criteria – same column

Let’s count the number of values under the following criteria:

  • Poland
  • China
  • Cyprus

The formula using COUNTIF function in Excel with multiple criteria will look like this:

=COUNTIF(A2:F15,{"Poland","China","Cyprus"})

Note: If you use Excel Desktop, make sure to select as many cells as the number of criteria in your COUNTIF formula and press Ctrl+Shift+Enter. Otherwise, the formula will only return the result for the first specified criterion.

Excel COUNTIF contains multiple criteria returned vertically

The COUNTIF Excel multiple criteria formula returns counts per each condition horizontally. If you replace commas with semicolons in the criteria section, the result will be returned vertically:

=COUNTIF(A2:F15,{"Poland";"China";"Cyprus"})

Excel COUNTIF multiple criteria – different column

In the example above, we used criteria from a single column. However, the formula will work even if you apply criteria from different columns. For example:

  • Poland (column A)
  • Toyota (column B)
  • Yellow (column C)

Here is the COUNTIF Excel multiple criteria formula:

=COUNTIF(A2:F15,{"Poland";"Toyota";"Yellow"})

Total of the Excel COUNTIF with multiple criteria

If you need to return a total value for the specified criteria, you need to nest COUNTIF formula with SUM as follows:

=SUM(COUNTIF(A2:F15,{"Poland";"China";"Cyprus"}))

The COUNTIF function follows the AND logic – i.e., you get a total number of values by criterion1, AND criterion2, AND criterion3, etc. But you can also update the formula to follow the OR logic for Excel COUNTIF function multiple criteria. 

How to apply Excel COUNTIF OR logic for multiple criteria

We’re going to add one more condition to our formula: it will not only count the number of the specified country-values, but also the car-values that correspond to each country. But COUNTIF won’t work here – let’s call its sibling – COUNTIFS. Here is the formula

=COUNTIFS(A2:A15,{"Poland","China","Cyprus"},B2:B15,{"Toyota";"Buick";"Acura"})

Note: As a separator, use commas for the first criterion and semicolons – for the second one. That way you will return values in the form of a two dimensional array.

Again, to return a total, wrap this COUNTIFS Excel multiple criteria formula in SUM:

=SUM(COUNTIFS(A2:A15,{"Poland","China","Cyprus"},B2:B15,{"Toyota";"Buick";"Acura"}))

COUNTIF and COUNTIFS are well-known functions that live not only within Excel, but also Google Sheets. If you operate this spreadsheet app as well, check out our blog post dedicated to the use of COUNTIF or COUNTIFS in Google Sheets. Good luck with your data!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free