Using COUNTIF to count specific items in an area

How many of the items in this list are red?

The COUNTIF function can be used to count only certain specific entries in a list. It requires two pieces of information.

The area containing the items to be counted and some way to tell which entries are required.

In the example below, which was created in the UK in the dead of winter, the colours of the items to be counted are contained within the range C3 to C12

If only the red items are to be counted, these are identified using the word RED. To indicate that this is literally the word RED, the text must be enclosed within speech marks.

Please note that a text match of this type is not case sensitive.

To find out how many hats are in the list, a similar procedure is followed. The only difference is that the item type is listed in the range B3 to B12. Once again, the text criteria is enclosed in speech marks and it does not matter whether upper or lower case text is used.

And if you want to know how many red hats are in the list, click here

Try it yourself using the examples below…

Download this example

Download all counting examples

Counting stuff in Excel

The COUNT function is used in Excel to find out how many items appear in a specific area of the worksheet.

Unfortunately for the unwary, COUNT only works for numbers. To get the most out of this family of functions, you need to know about COUNTA and COUNTIF as well

Try these short tutorials – the easiest one is at the top…

Count everything in an area
(How many entries have been completed?)

Only count specific items in an area
(How many of the items in this list are red?)

Count using two criteria
(How many hats in this list are red?)