Google Sheets has a lot of functions meant for analyzing data.
FREQUENCY() is one of them.
FREQUENCY() helps you count how often a given value in an array occurs within another array. Itโs a method of comparing one array to another โ quickly.
Because it auto-populates columns, it doesnโt take a lot of work to get your data.
At the same time, it also isnโt always the most elegant solution to the problem.
Letโs take a look at the FREQUENCY() function, how it works, when youโd use it, and when you might want to use something else.
Table of Contents
How Does the Google Sheets FREQUENCY Function Work?
The Google Sheets FREQUENCY() function works by comparing two โarrays,โ which means two selections of data. The first selection of data is the base set โ the data that is being analyzed. The second selection of data is the comparison set.
Itโs easiest to understand with an example.
In the above example, weโve made a list of 12 houses in Neighborhood Lane, along with their bedrooms and their bathrooms.
We want to find the frequency of bedroom sizes.
In one column, we enter in the bedroom amount we are trying to analyze for:
ย ย ย ย ย ย ย ย 1
ย ย ย ย ย ย ย ย 2
ย ย ย ย ย ย ย ย 3
Next to the โ1โ we enter in our code:
=FREQUENCY(B4:B15,F4:F5);
The first selection selects the entire list of bedroom amounts. The second selection selects the numbers being compared. And the # of Homes column auto populatesย with:
ย ย ย ย ย ย ย ย 1
ย ย ย ย ย ย ย ย 5
ย ย ย ย ย ย ย ย 5
ย ย ย ย ย ย ย ย 1
And this is correct. There is 1 one-bedroom house, 5 two-bedroom houses, and 5 three-bedroom houses. The 1 is the number of houses that are outside that rangeย (the sole 5-bedroom house).
When to Use FREQUENCY vs. COUNTIF
When people think about how to find frequency of a function, theyโre usually just thinking about getting a โcount.โ Which leads us to an observation: You can always just use COUNTIF().
In the above example, youโre instead using:
COUNTIF(B4:B15,F4)
And so forth. It gives you the exact amount. And we could also say something like โ>=2โ if we wanted a count of all homes that are more than two (or three, or so forth). But it doesnโt populate the entire column and it doesnโt give us a count of things that arenโtย one of the included range.
Click here for a full tutorial on how to use the COUNTIF function in Google Sheets.
Using FREQUENCY() for Distribution Chart
What is FREQUENCY() used for? In general, frequency is used for charting. If you select the columns and then create a chart, you could create the following chart. The data is perfect for identifying bedroom amounts across a number of homes.
But FREQUENCY() is really only intended for distribution charts. Thereโs no trick to the question regarding COUNTIF โ COUNTIF is almost universally preferred.
Calculating FREQUENCY() in Google Sheets
Now you know how to calculate FREQUENCY() in Google Sheets โ and how to use and analyze the data later. Ironically, the โFREQUENCYโ of a number is often best calculated by using COUNTIF, but there are exceptions, especially if there are a lot of values.
Google has a lot of surprising functions. So, if thereโs something that youโre thinking of doing in Google Sheets, you should always look it up first โ there may already be a solution.
