Many of my clients has asked me “How do I count the number of occurrence for a particular item?”  For example, how many answered with a value of 6 for question number 12 on my survey. Therefore, if a survey was passed out among a group of people, can Excel tally the score for each question? The answer of course is Yes! The Help screen from Excel provides the following definition and after you read it, you are probably still confused.

The FREQUENCY function returns a frequency distribution as a vertical array. For a given set of values and a given set of bins (or intervals), a frequency distribution counts the number of values that occur in each interval. 

The FREQUENCY function syntax is as follows: 
   FREQUENCY(data_array, bins_array)

The function returns the number of elements you specify in the data_array argument that fall within the intervals you specify in the bins_array argument.

Let me explain to you in laymen term: Here are the simply steps!!

First, the values you want to tally must be in a number format. If a value is in text form like Yes or No, assign Yes to 1 and assign No to 2. Create a table that represent the questions across the columns and each answer from each survey collected.

Picture1
Second, create a column call Bins where the number represents the values of the choices given on the survey.  Across the headings, copy the headings of the questions.

Picture2
Third, you create the frequency formula in the first cell right of the first bin number.

1. Highlight the empty column next to the Bins column, not including the column heading.

Picture4
2. Type the following formula as shown below

Picture5

  • A2:A11 represents the first column of choices (type in or highlight A2 through A11)
  • the second part of the formula represents the Bins(type in or highlight J2 through J14) then F4 key (absolute key) to lock in the range of cells.
  • YOU MUST HIT CTRL+SHIFT+ENTER to complete the formula as an ARRAY.

Picture6

Lastly, fill the formula across and you have a table that counts the number of occurrences for each question.

Picture7

Have Fun!

Advertisements