Excel - Counting Occurrence of Text Values

Here's a poser I was asked today. Someone had an existing spreadsheet that had a column containing text, but she wanted a count of the occurrence of certain values.

Let's look at a simple example of what she wanted.

A summary of how many fields contain "red", how many "blue" and how many "yellow" is required. Now I'm going to keep this simple but if it was possible to enter either "red" or "RED" or "Red" or, worse, "scarlet" then the solution would be a bit more complicated, though still possible - perhaps a future entry to come!

Here though the users have been told to use just the standard term, using lower case throughout. So the field only contains one of three possible values with a fourth value perhaps being an empty field.

Here's a simple solution. There are other solutions more complicated but more elegant in that they don't require the use of any extra cells in the spreadsheet. Again perhaps a future entry will look at these (there's more than one way to skin this particular cat as there are in most forms of computing!)

I've used the next three columns to calculate a numerical value registering the instances of each colour - a column being required for each of the three colours. In other words, I've not entered all those zeros and ones, the spreadsheet has used a formula to determine what value should be in each cell.

The solution uses the if statement which follows this syntax form

=if(argument being tested, value if true, value if false)

The equals sign ( = ) is necessary before the if so that Excel recognises that what follows is a formula and not a piece of text.

The argument being tested here is does the value of cell A2 equal the text within the quotes - ie red?

It does so the argument is true, therefore the value if true part of the formula is used which in this case is a numerical 1. Note that if we had wanted a text value we could have used quotes and text messagea as the value if true and value if false

e.g.: =if(A2="red","A red lover!", "What's wrong with red?")

I needed to type in the formula for the first row only. After that I selected those three cells and used the click and drag technique to copy them down the columns. Excel is clever enough to know that if you copy in this way it will amend the formula for each row to calculate from that row - instead of every row calculating using the value in the first row.

All we need to do now is add up each column for our summary. We could put a count at the bottom of each column and then use that or we could use the sum formula like this:

And the job's a good 'un!

Diposting oleh Abdullah Syafii