The Countif Function in Excel VBA

Written by darrin koltow
  • Share
  • Tweet
  • Share
  • Email

Learn to use Excel's Countif function from Excel VBA if you have a set of data containing records you don't want to count among those you do. It's possible to manually extract just the records you want to count, but this takes time and could result in your corrupting the data with a stray keypress during the extraction. VBA is free from such problems and allows you to call Excel's Countif function with a single VBA statement.

Other People Are Reading

Inserting Function

You can begin using the Countif function in VBA without knowing VBA's syntax or how to use its virtual objects. Record a macro where you manually insert the "Countif" function in a cell, then click a new cell and play back the macro. Excel VBA will insert "Countif" in the new cell. As you record the macro, you can type "Countif" into a cell directly, if you know the arguments you want to specify. If you're not sure of these arguments, use the function wizard by clicking the "Fx" button to the left of the text box above the centre of the worksheet grid. Excel will display a list of functions you can enter in the current cell. Double-click the "Countif" function from that list to make Excel prompt you for the function's two arguments.

First Argument

The first argument of "Countif" specifies the range containing the values you want to count. For example, you could type "ActiveCell.FormulaR1C1 = '=Countif(R[1]C[1]:R[4]C[1],''>0'')." This would make Excel count the negative numbers in the first four rows beginning with the cell one column to the right and one row down from the current cell. The "R[1]" text indicates one row down from the current cell and "C [1]" indicates one column to the right. You can make it easier to see what "Countif" is counting if you use a range name in place of a cell address like that just shown. For example, select the cells "A1" through "A4," then type the text "SalesFigures" in Excel's "Range name" box to the left of the formula bar. In the VBA development environment, type this statement:

ActiveCell.FormulaR1C1 = "=COUNTIF(SalesFigures,"">0"")"

This application of Countif tells you immediately that the function is counting sales figures.

Second Argument

The second argument of Countif is the criteria that the data in the range in the first argument must meet before Countif will count the data. In the VBA statement "ActiveCell.FormulaR1C1 = '=Countif(SalesFigures,''<0'')'," Countif will count only those cells whose numbers are negative. You're not confined to using just numeric criteria for the second argument. If the range you're specifying in argument one contains text, for argument two you can specify text criteria, such as "='cat'" to count only cells containing the text "cat."


Consider the scenario of owning an art supply and needing to count only your more expensive items. To do so with Countif, use the function's second argument to specify the minimum price a product must have before Countif will count it. For example, "ActiveCell.FormulaR1C1 ='Countif (SomeRange, ''>1.98'')" will count only those prices in SomeRange that are greater than £1.20.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.