### 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…

### 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…

### Percentage of a number using Excel

#### What is 7% of 2,500?

This is probably the easiest percentage calculation to do. Simply multiply the number by the percentage required.

In the example below, a tax rate of 7% is applied to an original amount of 2,500…

7% of 2,500 is 175.

To find the total amount payable, add the 2,500 and the 175…

Try it yourself using the examples below…

### Count everything in an area

#### How many entries have been completed?

In the example below, an ‘x’ has been used to indicate who has shown an interest in a neighbourhood watch scheme. To find out how many people are interested, the COUNT function might be used.

COUNT, like all functions, has a set of brackets that contain the details of the area containing the items to be counted.

The screen shot below shows the result of using COUNT on the column containing the ‘x’ characters.

The COUNT function only ever counts numbers. Since ‘x’ is a text character, the result comes out as 0.

To count numbers, text and pretty much anything else, use the COUNTA function. You can think of the A as meaning All or Anything.

The screen shot below shows the result of using COUNTA on the same column.

If dates had been used instead of a text character, the COUNT function would have been fine since properly entered dates are numbers in Excel!

Try it yourself using the examples below…

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

### Percentages in Excel

The use of percentages in Excel are not the easiest concept to understand.

The percentages you use with Excel are just the same as the ones you might calculate with a calculator but it helps if you think about them slightly differently when calculating in Excel.

Always enter a percentage in Excel as a number followed by a percent sign. If you work this way, you don’t have to worry about all that multiplying and dividing by 100!

Try these short tutorials – the easier ones are at the top…

## Percentage of a number

(What is 7% of 2,500?)

## Increase a number by a percentage

(Increase 24,000 by 4%)

## Decrease a number by a percentage

(Calculate a discount of 9% on a purchase of 120)

## What percentage does a value represent of the total amount?

(7 answers out of 12 were correct. What is the percentage score?)

## Difference between two numbers as a percentage

(Customer numbers changed from 2,800 to 3,200. What percentage increase is that?)
(Customer numbers changed from 3,500 to 3,400. What percentage decrease is that?)