The COUNTIF and COUNTIFS functions count the number of cells in a range that meet one or multiple criteria. They're easy to use, versatile, and help you quickly count across large datasets in a spreadsheet. Continue reading to learn how to use COUNTIFS and view COUNTIFS examples.

View live COUNTIFS examples

• What is COUNTIFS?
• COUNTIF and COUNTIFS Examples
• COUNTIFS and Pivot Tables
• COUNTIF AND vs COUNTIF OR
• Troubleshooting COUNTIFS formulas
• Slow COUNTIFS in Excel and Google Sheets
• Similar functions to COUNTIFS

What is COUNTIFS?

The COUNTIFS function counts the number of cells in a range that meet one or multiple criteria. Here is the COUNTIFS syntax:

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

Only one set of range and criteria is required. You can apply one or many criteria:

• criteria_range1 is the range of cells to evaluate
• criteria1 is the condition that must be met in the range

COUNTIFS with multiple criteria count the number of rows where ALL criteria are met, so each criteria set is evaluated as COUNTIF AND (count if criteria 1 is true AND criteria 2 is true, etc.).

Note that the ranges can be the same or different across criteria but they must be the same size (same number of columns and rows). The difference between COUNTIFS and COUNTIF is that COUNTIF is limited to one set of range and criteria, whereas COUNTIFS can apply one or many criteria. Here is the COUNTIF syntax:

=COUNTIF(criteria_range, criteria)

Note that COUNTIFS in Excel, Google Sheets, and Row Zero uses the same syntax and works the same way across each spreadsheet. The primary difference is that you can COUNTIFS larger datasets and dynamic datasets in Row Zero.

COUNTIF and COUNTIFS Examples

Below, you'll find COUNTIFS examples that demonstrate how to do a COUNTIFS formula - from a simple COUNTIF to more advanced COUNTIFS examples. You can view these COUNTIFS examples live in a spreadsheet here.

Simple COUNTIF and COUNTIFS examples

Here is a basic COUNTIF that counts the number of cells that match one criteria:

Here is a simple COUNTIFS with multiple criteria:

COUNTIF greater than, less than, and between

It's very common to use comparison operators to COUNTIF greater than, less than, etc. To evaluate equals, you don't need an operator. To evaluate greater than or less than for your COUNTIF criteria you need to include the operator in quotes with the criteria. For example, the formula for COUNTIF greater than 0 includes ">0" in the criteria:

COUNTIF less than works the same way with "<" in the criteria.

You can also COUNTIFS between values using one COUNTIF criteria as greater than and one criteria less than:

COUNTIF before, after, and between dates

You can also use the less than and greater than operators on dates to COUNTIF before dates ("<") and after dates(">").

COUNTIF between dates example

Similarly, you can COUNTIFS between dates by using both a ">" and "<" as criteria.

COUNTIFS NOT

COUNTIF NOT uses the "<>" symbol. Here's an example of COUNTIF NOT:

COUNTIF empty and not empty

COUNTIF empty uses empty quotes with no spaces (""): COUNTIF NOT empty uses just the not symbol in quotes ("<>"):

Create a Counter Column with COUNTIF

You can create a counter column using COUNTIFS to count the number of times a value appears in a range. This unlocks several ways to analyze your data including count and sort, filter by count, conditional formatting by count, counting duplicates, filtering duplicates, finding unique values, COUNTIF color, and other types of COUNTIF formatting.

How to Create a Counter Column

You can create a counter column with COUNTIF to count and sort, filter, and format your data. Here's how:

1. In a new column, write a COUNTF formula that sets the column you want to count in as your range and sets the criteria as the first cell to count.
2. Lock the cell reference using "$" and then drag it down your column. This will create a count of each value in the column.

From here you can sort by count, filter by count, format by count, etc. You can also use this as COUNTIF duplicates to count the number of repeat instances of a value to filter or delete duplicates.

Create a counter column with multiple criteria

You can use COUNTIFS to create a counter column with 2 criteria (or more) just as easily using two sets of ranges and criteria in your COUNTIFS:

COUNTIFS on large data set

Row Zero makes it easy to do COUNTIFS on big data sets. View a live example of a big COUNTIFS across sheets. Row Zero is 1000x more powerful than traditional spreadsheets so if your data is too big for COUNTIFS in Excel or you're struggling with a slow COUNTIFS in Google Sheets, try Row Zero. Row Zero is a good alternative to Excel and Google Sheets for big data.

Dynamic COUNTIFS

Row Zero makes it easy to connect your spreadsheet directly to your data source (Snowflake, Postgres, Databricks, etc.) and import connected tables which maintain a connection to your data source and update dynamically as your source data updates. You apply COUNTIF formulas (and other formulas, charts, and pivot tables) on top of this connected data so that your COUNTIFS automatically update as data is refreshed.

COUNTIF Across Multiple Columns Using SUM

You can COUNTIF multiple columns by combining COUNTIF and SUM: The COUNTIF counts occurrences in each column and the SUM adds up the counts across all specified columns.

COUNTIF wildcards

You can use wildcards in COUNTIF to count across messy datasets or to analyze your data in unique ways. The asterisk wildcard (*) is evaluated as any sequence of characters and is helpful when counting values with similar names (.e.g Fruit Co, Fruit Co. and Fruit Company in the example below). The question mark wildcard (?) is evaluated as any character. For example, "???" could count every value that is 3 characters.

COUNTIFS and Pivot Tables

Pivot tables can be used as an alternative to COUNTIF functions or used in tandem with them. Like COUNTIF, pivot tables make it easy to count values and filter by criteria.

Pivot table count if

You can use COUNT and Filters in pivot tables to get to same results as COUNTIFS:

Add COUNTIFS to a pivot table

Row Zero lets you apply column level transformations to pivot tables so you can add COUNTIFS to a pivot table:

COUNTIF on a pivot table

Row Zero lets you write a COUNTIF on pivot table data by referencing a column in the pivot table:

COUNTIF AND vs COUNTIF OR

The COUNTIFS function counts where ALL criteria are true and uses AND logic. If you want to count if ANY using OR logic, you have a few options:

Count if any by adding COUNTIF formulas

This example "=COUNTIF(A2:A15, "Banana")+COUNTIF(A2:A15, "Apple")" counts from from the first COUNTIF or the second COUNTIF.

Use SUMPRODUCT for OR logic:

In this example "=SUMPRODUCT((A2:A15="Banana")+(A2:A15="Apple"))", the SUMPRODUCT formula creates an array for each COUNTIF equivalent and "+" combines the two, resulting in 1 wherever either condition is met. SUMPRODUCT sums them up.

Troubleshooting COUNTIFS formulas

There could be a number of reasons why your COUNTIF does not work. If you need COUNTIF help, here are tips to troubleshoot COUNTIF errors.

10 COUNTIF tips

1. Ensure each criteria range is the same size (same number of columns and rows)
2. Generally, criteria that is not a cell reference or equal to a number should be enclosed in quotes. This includes greater than, less than, etc. (e.g. ">0" for COUNTIF greater than 0).
3. COUNTIF and COUNTIFS are not case-sensitive. Use SUMPRODUCT with EXACT() for case-sensitive counts.
4. COUNTIF AND vs COUNTIF OR - Remember that COUNTIFS evaluates each criteria as count if AND. You can COUNTIF OR by adding multiple COUNTIF formulas or using SUMPRODUCT.
5. Use "<>" for "not equal to". For example, "<>0" counts cells not equal to 0 and "<>Pizza" counts cells not equal to "Pizza".
6. Use "" to count blanks and empty cells and "<>" to count not empty cells.
7. Use cell references for dynamic counting and use "$" to lock cell references when dragging COUNTIF formulas.
8. If your COUNTIF criteria is a reference to an empty cell, the COUNTIF function treats it as a zero value (0).
9. Use wildcards in COUNTIFS for partial matches - asterisk (*) matches any sequence of characters (e.g. "Fruit Co**" will count Fruit Co. and Fruit Company) and question mark (?) matches any single character (e.g. "T??" would count all three letter words that start with T).
10. The difference between COUNTIF vs COUNTIFS is that COUNTIF is limited to one set of range and criteria, and COUNTIFS can apply one or many criteria.

Slow COUNTIFS in Excel and Google Sheets

Functions like COUNTIFS and SUMIFS evaluate each row in a specified range, so running them over large datasets can degrade performance. The Excel max row limit is 1,048,576 and Excel slows down well before that with heavy usage of formulas like COUNTIFS, SUMIFS, and XLOOKUP. Google Sheets limits are similar.

Try a more powerful powerful spreadsheet

If you have a slow COUNTIFS in Excel or Google Sheets try a more powerful spreadsheet like Row Zero. Row Zero is the world's fastest and most powerful spreadsheet, so you can make your COUNTIFS faster in Row Zero and run SUMIFS and COUNTIFS on very large datasets.

Speed up COUNTIFS with helper columns

In addition to trying a faster spreadsheet, you can also make COUNTIF formulas faster with helper columns. Here's how create a helper column:

1. In a new column, write a formula that evaluates your criteria using IF or IFS and returns just 1 or 2 values.
2. Write a COUNTIF where the criteria evaluates the helper column for this new value. The COUNITF will solve faster since it only has to evaluate 1 unique value instead of many unique values.

Similar functions to COUNTIFS

There are several functions similar to COUNTIF to analyze big datasets in a spreadsheet:

• COUNT: Counts the number of non-empty numeric or temporal values
• COUNTUNIQUE: Creates a table of all unique values with their counts
• SUMIFS: Sum values in a range that meet multiple criteria.
• AVERAGEIFS: Averages values in a range that meet multiple criteria
• MAXIF: Finds the max value in a range that meets criteria
• MINIF: Finds the min value in a range that meets criteria
• Format if with conditional formatting

COUNTIF vs COUNTIFS vs SUMIFS

COUNTIF and COUNTIFS are very similar functions and are sometimes confused with SUMIFS which is similar. The main difference between COUNTIF and COUNTIFS is that COUNTIF is limited to one criteria whereas COUNTIFS can use one or many criteria. SUMIF is similar to COUNTIF but sums rather than counts the values that meet the criteria.

Conclusion

The COUNTIFS function is a powerful spreadsheet formula for counting values that meet one or many criteria. If you need COUNTIF help, step through the COUNTIF examples above and you'll soon feel comfortable with simple COUNTIFs and be able to move on to more advanced COUNTIFS formulas. If your COUNTIFS in Excel is slow, or your Google Sheets COUNTIFS is freezing, try using Row Zero. Row Zero is a next-gen spreadsheet built for big data that makes it easy to use COUNTIFS formulas in big spreadsheets.

Try Row Zero for free

Related Content

• How to use XLOOKUP
• Best Online Spreadsheets
• How to remove duplicates and count uniques
• Excel vs Google Sheets vs Row Zero
• Best Google Sheets Alternative