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:
Are you ready to learn how to apply them in your worksheet? Let’s begin!
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)
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:
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:
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.
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.
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.
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!
