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. We’ve blogged about how to connect BigQuery to Excel using Coupler.io, a solution for automatic data transfer from different apps and sources to Excel.
Learn more about Coupler.io as well as the available Excel integrations, such as Harvest or Google Sheets, and try them out 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!
