How to use COUNTIF function in Excel

Updated on: March 17, 2023

Have you ever wanted to know and locate a specific type of cell, but you can’t because of the vast amount of values you have on it? Now you can do so, thanks to the COUNTIF function! 

This tool allows you to count the number of cells containing criteria or conditions and can be very convenient when you need to give your boss a specific value from the data you have on your worksheet.

So, the COUNTIF function works with two arguments: Range and criteria. Both of them are essential to work within the next syntaxis:

=COUNTIF(range, criteria)

  • Range: This is the group of cells you want to report on.
  • Criteria: Is the matching text, number, or expression within the cell range to a specific condition.

But of course, there are distinct criteria types, and each of them works differently from the others, and its writing changes a little bit in the syntaxis. So here are the main 4 criteria you can use in Microsoft Excel:

  • Text Criteria:
  • Cell reference Criteria:
  • Numeric Criteria:
  • Logical Expression Criteria:
  • Finding Blanck Cells:

Ready to know how they work? Let’s go!

Text Criteria:

Alright, this function works when you want to find a specific name or content written as text. So, the syntaxis will be:

=COUNTIF(Range;“Criteria”). Example: =COUNTIF(A1:A11;”Jane Doe”).

To use them properly, you can follow the steps below:

  1. First, pick a cell apart from the table.
  2. Next, type the syntaxis, remembering to open the parenthesis.
  3. Select the cell range.

Note: If you have a table, you can choose the whole data column by typing the name of the table and the column header between brackets.

  1. Type the semicolon icon to separate the range from the data.
  2. Enter the criteria you need between quote marks.
  3. Close the parenthesis.
  4. Hit the Enter key and get your result.
8RH8QbAUEs4RQM5ath54Kpf0x vGyuaHnodQwDPLd64aNQK H7KGdR65Dm0Z0DNF63f88MFSab6AnLLWwId4jRC6P91ZpgHQLt7X2R8yv XIQqMWUqETNa0S7LdDxMY 5FuKX w2FHvPI5VKZYlkPk
1fxQxnLSDjHNx3G0xGIisPt1AV8s8b5 HZa1k16vYi1MFXDWtYFVuSzkCJDAriBKYxCcU W5nr0 4fdwUWQVsWTvAOFpHIhhAZMjSpg9KC4zo82QnGpuXC WoOHAbbz3bUGNGRPSg7wTN05ZD3tvmmg

Note: Be careful when entering the criteria. You must type the exact text. The minimum character will change the whole result.

Cell reference Criteria:

On the other hand, the cell reference criteria are when you can search for a specific value or text from the data set simply by using another cell reference. Check out how you can write it:

=COUNTIF(Range;Cell reference). Example: =COUNTIF(A1:A11;C4).

C W6ujMnVvn3kCwclORiMsLe EgU5lvNKGH4wBAt3 O3a21CITv xvyvliupwbijwg 1aADAMc8XeNzckrHhbD MxIOIsb1HkjF NNNCNnLli9kjsmYHTIIUKIoz6ZdkHYZA56VEMKzcWZ4vDVcDLTg
8CFoZu8wxazai Ev0z10JdsVtPW2HH8DYwZYz nIbfjU 3 oh W9nNd26UeIOc32RYTZPSrv57PKsYP

Numeric Criteria:

This can be handier when working with numerical values, not only whole numbers but also prices, liters, or any numerical measurement. You only need to type this syntaxis:

=COUNTIF(Range;The particular number you’re looking for). Example: =COUNTIF(A1:A11;20).

An SmUgnx2B4VJkQDa432 n94ysG9ciBE8o9KJMXukxeTrduslUPQnWvePYe3oC8SXmYzum2bJBU51sv1rjK AWjOah4LcLgUHh QwBboGsvoOzJ19JTuo4t 2WQmY5vfYfAQPqXLOtbbWTwp3wjaFA
xw1AfBhyQuMYOby8YzTXbjVOWK2U6t7ml6HxDTLYZABDcunZCqEOMJty jQvfsZwz1E7f YKYZd9qVkcoerKbr9MQXwCXmwRFOaBY HWYTQ5pnt9K1qudaRibNsYBGZF4m5BEjWLELoHJEJl4oT4m g

Logical Expression Criteria:

The logical expressions respond to the standard logical mathematical operators:

  • >= Greater than or equal to.
  • <= Less than or equal to.
  • <> Not equal to.

Although the argument says “Equal to,” you don’t need to type the equal sign due is implicit. And in the other hand, you can use these operators and combine them to make more specific operations as the following:

Logical expression with a numeric value:

This operation works better when looking for a range and not anything specific. Hence, as the Numeric Criteria, this can be applied to whole numbers, prices, or any type of number. Moreover, you have two syntaxis by which you can access this operation:

  • =COUNTIF(Range;”LogicalOperator and Number”). Example: =COUNTIF(A1:A11;”>3”).
wWNinklMSbO58pDnka4LgA37Uu5LtNINfkG LRFSfIljr94CzA31bw2M6qyePdyYTSX0dNRNY1vRZlmmiGb7sW6BMoNhQGgc4J zI3VzWcwqpjuzw79IHuz5 QMGsACnCNU9y3JtrdHuJ7uAKRwF6dU
  • =COUNTIF(Range;”LogicalOperator”&Number). Example: =COUNTIF(A1:A11;”<”&4).
lyLkEwGrZxiVr0Oi4Rnc5tMlHpusvjxMSpvU7ZGuyxEESts UbLjBJlmOZN XxknIAQij3qZBxJEsg6OUPPkak4len6TQndqIR 85wuQjiSX9 ClW6f6azfTb4FUGfk9SBWmm iYXR9s8Ltdd P7a0Q

Logical expression with a cell reference:

This expression only works when using the logical operator enclosed between quote marks followed by an ampersand icon (&) and selecting the cell reference. Check the syntaxis:

=COUNTIF(Range;”LogicalOperator”&Cell reference). Example: =COINTIF(A1:A11;”>”&C4).

LuTKzlonZBVw FYmhbbbOJpa9NpEezjGbqI w3fazadlnrdVPH2tPJcjnpkR7JmtjgMYp522ABCviqdwfGyOx4 AdBo2CV0w7t8GrzqPnyekWaP2PiKAWD1QddjSA5Dz lHyn2qEF1ktRRIJW1vEaJk

Finding Blanck Cells:

Also, this fantastic tool allows you to find those blank cells in your data set. You only need to type this syntaxis:

=COUNTIF(Range;“”). Example: =COUNTIF(A1:A11;“”).


Do you want to know more?

Now you’ve learned how to use a COUNTIF function! All that remains is to try it yourself and find more uses for this aid! But if you need something advanced, you can learn about the COUNTIFS function, which despite seeming to be the same operation, has some variations. Also, you can use the Pivot Table o the Slicers when using this amazing tool. Finally, remember to check out our Excel course here in Skillademia!

Lorena M. Rodas leverages her experience across film, writing, and production to make complex tech concepts accessible through storytelling. With a background spanning sci-fi, AI, and emerging tech, Lorena translates her depth of knowledge into engaging, educational content. She is an expert at decoding high-level topics to reach broad audiences.
Access 4,242+ video lessons. Instantly.
Start Learning Now →
crossmenu linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram