4 uses for the COUNTIF function in Excel

Writer
Updated on: March 15, 2023

Learning how Microsoft Excel works will guide you to work more neatly and to a higher standard. And you may have discovered that the same function can do many operations, like the COUNTIF one. However, especially with this tool, you can do much more than find specific values. Check out these other uses:

  • Count Booleans
  • Using COUNTIF wildcards:
  • Using the OR logic
  • Counting with the AND function

Are you ready to learn how to apply them in your worksheet? Let’s begin!

Count Booleans

When you have a Boolean condition, Excel will treat it as a mathematical value rather than a text one. That’s why you can count them without using the double quotes marks (as though the text criteria). So when it says “TRUE,” the program will internally translate its value as 1 and “FALSE” as 0. 

Having this in mind, the syntaxis will be:

=COUNTIF(Range;TRUE). Example: =COUNTIF(A1:A11;TRUE)

KH0oyFYdgMCA9an4 nNPuxKuQBkqvot2Lvkq3dYoOcSfunvz6LEsEeSh3TirzMyjl6 2OOIjAZQMkVDA iMyBqV13gQ9rugDwLy4LJuAnu
tkqt7K3au75LoIdtaf2pYZRDcsx0gzK922hmXJCBoPXRKTOzEu6zhT6Swmc0p45BQakiodlyAnV3W2MJi2Z8egu XKTlK8dZbJtA2VbvZJ0UmFeVn0jm15k9SOkkiTkCRUNzxeReF T9J qttgJ1zk4

Using COUNTIF wildcards:

It happens that you may want to count similar items. But unfortunately, they're not exact. Relying on them one by one can take up a lot of time. Relax! You can use a wildcard to group all those criteria you need. To do this, just type the following syntaxis:

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

After writing the criteria, notice that you must put an asterisk before closing the double quote marks. This will tell Excel that you are looking up all cells that contain that word due to the asterisk being a placeholder. Check this other example:

1PxrCoJ8d W NCtlaSUCGkOAQuJ11hIvBFUliEJPM19eUj22cLHJL0O aUReQov i O4kWCl3D5S fdIFceo 6j4KEIND0YPa77g4Ja2bBlNaO8WhThzy4NpfGvbAMeeEXu7urYKrlh70Rir4yphtRk

On the other hand, you can also use the other two signs, but they’re not commonly used. The first one is the Question Mark, which will show you which cell has a single unknown character:

BcMplTo5RgSbDCdVP8h KPQY7nN7eMiLUElxqkuqi5tkDSKa8gGUPWNZeQa4hww MzoJCwoZ671iVs19ngVj0Z8ej0pKmYg63JIA5V2k6G5VNekkIW3JnFrtZjfXM7N efmieAfI6UQUQdy6eFUYgfI

Using the OR logic

So, you can also use the COUNTIF function when wanting to count several criteria. It sounds like a big formula, but it’s very intuitive. You just need to sum using the following syntaxis:

=COUNTIF(Range;Criteria)+COUNTIF(Range;Criteria).

Note: You can use any criteria you’ve learned, which will also work.

6wQRkC67QutCq4zuShTB09cVZuj8UXzqA1JAftxvg5WIOETtw35o69NPXd2Mt89oj1F6qsCoOWTVJkwk2NWdHJN

With this function, Excel finds what you asked in the first parenthesis and then sums it to the second result of the last parenthesis, and so on.

Counting with the AND function

Last but not least, the “AND” logic will help you find and count those cells that can meet more than one criterion. 

This is the less accurate and straightforward function you can use, and in this case, even when talking about the COUNTIF operation, it works better with numbers rather than texts. Finally, its syntaxis from now on changes to start with the “=AND” even if it also counts the number of cells.

Take into account that the COUNTIF function only works with one criterion at a time, instead of the AND function, with will evaluate every cell individually, which may be highly useless with a massive data set. That’s why you can also use the COUNTIFS function, which appears alike but has some extra parts you can find handy.

Do you want to know more?

Up to this point, you have noticed how great could be the COUNTIF function, and all the things you can do with these new 4 tips!

If you want to explore more, check out our Excel course here in Skillademia and find these handy tips to master Excel. Good Luck!

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 2,701+ video lessons. Instantly.
Start Learning Now →
crossmenu linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram