Excel Corner: Conditional Counting - COUNTIF

February 28th, 2008

This is from the 2/28/2008 issue of the Monitor, a publication of Windmill Software.

Excel Corner: Conditional Counting - COUNTIF

Excel’s COUNTIF function is often useful when analysing your data. Here are a couple of examples. They both are being applied to a column of data in cells B2 to B100.

To count the number of negative readings:

=COUNTIF(B2:B100,”<0″)

To count the number of readings which are between 17 and 18:
=COUNTIF(B2:B100,”>=17″)-COUNTIF(B2:B100,”<18″)

Using the Functions in your Macros
You can use COUNTIF in your VBA code. Here we are counting the number of negative readings and storing the result in a variable called BelowFreezing.

BelowFreezing = Application.COUNTIF(Sheets(”Sheet1″).Range(”B2:B100″), “<0″)

Counting over Scattered Cells
The COUNTIF function checks to see if the specified cells meet one condition. You can use cell references as in our example, or a named range. The range of cells can spread several rows and columns, but must be contiguous.

=COUNTIF(A2,A4,A6,”<0″)

won’t work. However, you can sum COUNTIF functions to get the required result:

=COUNTIF(A2,”<0″)+COUNTIF(A4,”<0″)+COUNTIF(A6,”<0″)

Having a Cell Contain the Condition
In our examples we have entered our conditions as numbers. You could instead use a cell reference. In this example the cell C1 holds the numerical part of the condition (the operand):

=COUNTIF(A2:A100,”< "&C1)

Note the less than symbol is enclosed in inverted commas and the & sign which precedes the cell reference.

Counting against more than one Condition
With COUNTIF you cannot count against more than one condition: such as the number of times both the temperature was over 0 C and the humidity was below 50%. Instead you could use DCOUNT, an array formula or a Pivot table.