Count using two conditions

How many hats in this list are red?

There are several ways in which this problem may be solved. The simplest is first to determine which of the rows in the table contain Red Hats. This is done using the logical function AND.

The AND function needs two or more parameters. In this case there are two questions to be answered. ‘Is the item a hat?’ and ‘Is the item Red?’.

For the first row, these translate into B3 = “HAT” and C3 = “RED”. Once again the text must be enclosed in quotes and the match is not case sensitive.

If all the criteria in the AND function are met, the function returns a TRUE value.

The calculation is then copied for each of the other rows in the table.

Any row containing a Red Hat will have a value of TRUE. To find the number of Red Hats, COUNTIF is used to count all entries with a value of TRUE.

TRUE need not be enclosed in speech marks as it is a logical value and not a text value.

Since the individual TRUE and FALSE values do not need to be seen, the answer can be moved to E14 and column D can be hidden.

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?)