IQ Accounting Solutions

The COUNTIF function in Excel is used to determine how many entries in a list meet a certain criteria.  The criteria can be numeric, such as “greater than 100” or text, such as how many start with a certain letter or contain a certain piece of text.

The basic format is =COUNTIF(range of cells to evaluate, criteria).  The range of cells can be an actual cell reference or a range name.  Let’s look at some examples of how we can put it to use.

Here is the simplest example of how to use COUNTIF.  Let’s say you want to know how many of the sales were exactly $60.  In the spreadsheet above, the formula =COUNTIF(C1:C5,60) would return the answer 3.  C1:C5 represents cells 1 through 5 of column C, and 60 is the number we want Excel to count.

In the previous example, Excel assumes that your criteria is “equal to”.  For anything else, you need to include your criteria in the formula.  You also need to surround the criteria with quotation marks.  So if you want to know how many sales were greater than or equal to $100, the formula would look like this =COUNTIF(C1:C5,”>=100″) .

The rules for evaluating text are similar, but need a little explanation.  First the text you want Excel to look for always needs to be surrounded with quotation marks.  So if you want to count the number of times Blue widget occurs in the description column, you could use the formula =COUNTIF(B1:B5,”Blue Widget”).  That will show only descriptions exactly equal to Blue widget, which is 1 in this example.

What if the items you want to count don’t all have exactly the same description?  In these situations, you can use the * as a wildcard.  So if you enter the formula =COUNTIF(B1:B5,”Blue*”) you will discover that only 1 entry starts with blue.  Or you can add another wildcard in front of the word blue, like this, =COUNTIF(B1:B5,”*Blue*”) and the result will be 2, because two lines have blue somewhere in the desctiption.  And  =COUNTIF(B1:B5,”*Blue”) will show that none of the descriptions end with blue.

I want to talk about one last variation.  In all of these examples, we have put the number or text that we want Excel to use for comparison right in the formula.  But you may want to count the entries that match the contents of another cell.  Counting entries that exactly match another cell is much like our first simple example.  You just substitute a cell address for the number.  To find out how many sales occurred on the date that is entered is cell A7 (2 in this example), the formula would be =COUNTIF(A1:A5,A7).  But things get tricky when your comparison is anything other than “equal to”.  When comparing to another cell, the operator needs to be in quotes, such as “>=” and the & character needs to precede the cell address.  So the formula to count the number of sales that occurred on or after the date in cell A7 would be =COUNTIF(A1:A5,”>=”&A7).