A to Z of Excel Functions: The COUNTIF Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the COUNTIF function.

The COUNTIF function

This function counts the number of cells that meet a particular criterion; for example, to count the number of times a particular city appears in a customer contact list.

The COUNTIF function employs the following syntax to operate:

The COUNTIF function has the following arguments:

• range: this is required and represents the range from which you want to count the cells meeting the specified criterion
• criterion: the condition to be met.

It should be further noted that:

• COUNTIF ignores upper and lower case in text strings.  Criterion is not case sensitive, so “red” and “RED” will match the same cells
• wildcard characters are permitted.  The characters, question mark (?) and asterisk (*) can be used in criterion.  The question mark matches any single character, whereas an asterisk matches any sequence of characters.  If you actually want to find a question mark or asterisk use the tilde (~) in front of the required character
• COUNTIF is pedantically precise.  Therefore, make sure your data does not contain erroneous characters.  In particular, when counting text values, make sure that text doesn’t contain leading, excess or trailing spaces, inconsistent use of straight / curly quotation marks or non-printing characters.  The CLEAN removes all non-printable characters and TRIM (a useful text manipulating function that removes excess spaces) functions can eradicate most of these issues
• range names may be used with COUNTIF if required
• range can be a range in the same worksheet, a different worksheet or even a range in another workbook.  However, if you refer to a second or subsequent workbook, these workbooks must be open for COUNTIF to work as intended, otherwise #VALUE! will be returned
• the wrong value may be returned for long strings.  The COUNTIF function returns incorrect results when you try to use it to match strings longer than 255 characters.  However, there is a workaround available.  You may use the CONCATENATE function or the concatenate (&) operator, e.g. =COUNTIF(A1:A7,”long string”&”another long string”)
• if no value is returned where one is expected, check to see whether the criterion argument should be in quotation marks, e.g. “>=14”.

 Please see my example below: 

It should be further noted that the COUNTIF function will not count cells based on cell background or font colour.  However, Excel supports User-Defined Functions (UDFs) using Microsoft Visual Basic for Applications (VBA).  For completeness, here is an example of how you can count the number of cells with specific cell colour using VBA.

1. Open Microsoft Excel then press ALT + F11 to show Visual Basic Editor window
2. On the ‘Insert’ menu, select ‘Module’ to create a module. Then write the following script:

1. Close VBE window and return to Excel
2. Test the UDF using example data (see example below)
3. In cell D3, write the function

• (note UDFs do not automatically capitalise like built-in Excel functions)

1. In the range_data argument, select cells C2:C31
2. In criteria argument, select cell F1
3. Press ENTER.  In cell F2 the result is 6.  It means the number of cells with this colour is six

1. You can also test for another colour. Change the colour in cell F1 as required
2. If you save this example as a workbook, say Count Cell Color (yes, that’s right, I appear to be advocating US spellings!), save it as an Excel Add-In (.xlam) format
3. This way this UDF may be used in other files. Open Excel on the computer that you want to install the Add-In. Then, open the ‘Add-Ins’ dialog box by clicking ‘Add-In’ on the ‘Developer’ tab
4. In the ‘Add-Ins’ dialog box, click the ‘Browse…’ button so that the ‘Browse’ dialog box is displayed

1. Go to file location that add-In file is saved. Choose the file and then click ‘Open’
2. On the ‘Add-Ins’ dialog box make sure that the add-in checkbox is checked. Then click OK
3. The Count Cell Color UDF has now been installed and is ready to use.

We’ll continue our A to Z of Excel Functions soon. A full page of the function articles can be found here.