Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]

If for every countif() I write excel paid me a dollar, I would be a millionaire by now. It is such a versatile and fun formula to work with that I have decided to write about it as third post in our spreadcheats series.

1. Using COUNTIF() to replace pivot tables: We all know that you can use countif() to replace pivot tables for simple data summarization. For eg. if you have customer data in a table and you would like to know how many customers you have in each city you can use countif() to find that.
   countif("master-data-range","city name")
   More on this method of using countif and 4 other ways of using excel if () formulas
2. Counting Valid Phone Numbers in a Range: Using operators < and > in countif() you can findout valid phone numbers in range like this: countif("data-range",">"&1000000000)
3. Finding number of customers in a city based on their phone number: This trick may not work perfectly. We can use countif("data-range","22*") to findout total customers in Mumbai (since all Mumbai phone numbers begin with 22)
   Note: This method works as long as phone numbers have identifiable calling codes and stored as text. To covert a number to text you can use text() or append an empty space to the number.
4. Pattern matching: Often when you extract data from other sources and paste it in excel it is difficult to process it when the formats are not consistent. For eg. when you copy address data of a bunch of customers and need to know how many customers are in “New York” you can use countif like this: countif("data range", "*new york*"), the operator * tells excel to match any cell with new york in it, not necessarily at the beginning or end of the cell.
5. Counting positive numbers in a range: Again we use the > operator to count the positive numbers in a range like this: countif("data-range",">0").
   A very good use of this trick is when you need to calculate average of a bunch of numbers but need to exclude zeros: sum("data-range")/countif("data-range","<>0")
6. As a replacement to FIND(): Excel FIND() is powerful formula to find if a particular text occurred in another text. But one problem with find is it returns #value! error if the value you are looking for is not in the input cell. What if all you need to know was whether your cells had a particular value or not?You are right, you can use COUNTIF() for that too, like: countif("cell-you-want-to-look","*hilton*") will return 1 or 0.
7. For sorting text: Read more on this at sorting text using excel formulas
8. Findout the number of errors in a sheet: The beauty of countif() is that you can even count error cells. For eg. you can use it like: =COUNTIF(1:33,"#VALUE!") to findout how many #VALUE! errors were there in the rows from 1 to 33. This can be useful if you are building a complex model and need to keep track of errors.

Most of the tricks should work with SUMIF() as well.

If you like this, read the other posts in the spreadcheats series. It is a 30 post series (3 posted so far) that aspires to make YOU very good in using excel to solve day to day problems.