Countif and Countifs

I thought I would share some details about the Countif function that I learnt today.

The COUNTIF function counts the number of cells in a single range that meet a single criteria. It takes the form

=COUNTIF(range, criteria)

range can be a single cell, a single column, a single row or a range of x cells by y cells.

criteria can be a number (e.g. 3), a comparison (e.g. <=3), a cell (e.g. C3) or a word (e.g. Alan).

So, for example if we have a list of goods types, the dates each one was sold, the number of items sold and the total cost, we could create a table such as this one;

In order to count on how many days Beverages were sold we could create a Countif function like this:

Where the Range is the column labelled Good Type in the Table named Table2. The Criteria is the value shown in cell I4 (i.e. Beverage).

We may want to count how many days we sold more than 30 of an item. So we would want to count how many times the number in the Items column was more than 30. In this case the function would be.

Here the criteria is written as “>=30”.

Choosing a value

But what happens if we want to change the value 30 to something else?

We may want the function to refer to a value in a cell and use the cell reference rather than the value.

If we write the criteria as “>=J8” then the result is zero.

In order for the function to work properly we need to concatenate the operator with the cell reference:

“>=” & J8

This allows the value in cell J8 to be changed so that the result of the function updates cell L8 automatically.

COUNTIFS

Where COUNTIF allows you to use one criteria and one range, the function COUNTIFS enables you to check several criteria against several ranges. Note that each subsequent range must have the same dimensions as the first one.

So, for example, to count the number of times when more than 30 items were sold for a cost of more than £1000 it is possible to create a function like this:

However, if we want to change the number of items and the total cost then we could create a small table.

We would then need to modify the function to concatenate the operators with the cell references.

The High Item and High Cost values could then be updated to change the Count value.