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:
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.
1. Highlight the empty column next to the Bins column, not including the column heading.
- 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.
Lastly, fill the formula across and you have a table that counts the number of occurrences for each question.