How to use the Excel Countif formula

Excel’s a brilliant way to organise data, and it’s even better if you turn to formulas to count data. The Countif function is one of the most useful, selectively counting data in your spreadsheet, letting you make sense of what you’ve got. We’ll show you how to use this feature, based on the inventory spreadsheet we set up in our How to use validation in Excel feature.

1 SET UP TABLE

Our spreadsheet was used to track a household inventory, so it makes sense to count up how many items of each type we’ve got. To do this, we need to make sure that our summary table has the same headers as the list of categories. In our example, it makes sense to copy the list of categories from the ‘Type’ tab and paste them into cell E6. In cell F6 we’ve created a header called ‘Number’.

2 CREATE THE FIRST COUNT

Countif counts how many cells in a given range have a value that matches your search criteria. What we want to do is count how many entries we have for each category type we defined, such as how many books we own. To do this, we have to fill in the Countif formula, which takes the form: =countif(<range>, <criteria>). The formula we want in cell F6 is, =countif(B:B, E6). This tells Countif to look at column B in its entirety (our Type column) and count how many cells contain the value stored in E6 (in this case, Book).

3 IGNORE EMPTY CELLS

You’ll see that the value in F6 is now the number of books that we own. Adding extra rows into the main sheet will increase this count every time a new book is added. Click cell F6, then click and drag the square in the bottom right corner down until you reach the bottom of your list. This will copy the formula downwards, giving you a count for each category of item.

You’ll also see that there’s a green triangle at the top of each cell, which lets you know that there’s a warning. In this case, Excel is telling you that the formula is looking at empty cells. That’s fine in this case, as we want the freedom to add new rows and have the countif function updated. Select all of the cells with this error, click the exclamation mark icon and select Ignore Error.

4 USE DIFFERENT CRITERIA

By default the criteria of Countif defaults to ‘=’; however, you can use different operators, such as greater than (>) or less than (<) if you’re looking for numerical values. For example, we could search our spreadsheet for high-value items (say  more than £250) to see how many we owned. To do this, in E3 enter ‘High value items’. In cell F3 enter the formula ‘=countif(C:C, “>250”). This searches the ‘value’ column and counts how many entries are greater than £250 (note you don’t need to enter the pound sign in the formula, but you do need the quote marks). Tell Excel to ignore blank values, as in Step 3.

5 PUT IT ALL TOGETHER

While Step 4 tells us how many high value items we have in total, it doesn’t give us a breakdown by category. If we want to do this, we need to use the Countifs formula, which lets you specify multiple ranges and criteria. It works like this: =countifs(<range1>, <criteria1>, <range2>, <criteria2>,… and so on. Only cells that match all of the ranges and criteria are counted.

In our example, create a new header in G5 called ‘Number of high value items’. In cell G6, enter the formula, =countifs(B:B, E6, C:C, “>10”). The first part of the formula (B:B, E6) counts the cells that match the category type; the second part (C:C, “>10”) looks for items that cost more than £10. We’ve lowered the limit here to account for books being cheaper. You can now copy this formula down, editing the price limit (“>10”) to suit each category.