The Frequency and COUNTIFS() functions are both very convenient ways of preparing frequency distributions that we can use to draw a histogram. The purpose of this page is to demonstrate both the frequency functions and the COUNTIFS() function in this context. The page will also demonstrate that the frequency function can be used either as an array function or as an ordinary non array function. The way they work is different and the answers they give are initially different although with a little bit of effort we can make them the same!

Frequency ARRAY Function

In the Excel workbook that accompanies this page (link at the bottom of the page) you will find a large amount of data: the temperature recorded somewhere on earth every month from 1907 to 1972. I wanted to prepare a histogram to see the distribution of temperatures over the ranges, or boundaries, of:

0 – 10 degrees

10 – 20 degrees

and so on

With the temperature data in a range named temp, this screenshot shows what that looks like:

Screen Shot 2013-12-20 at 08.27.11

To program the frequency column with the array version of the function,

  • select the range H9:H17 and type =FREQUENCY(temp,G9:G17)
  • now press Ctrl+Shift+Enter or, on a Mac, press Cmd+Enter

The range H9:H17 should now fill with the appropriate values for each temperature range.

You can now draw your histogram to look like this

Screen Shot 2013-12-20 at 08.31.19

That’s it!

Frequency Non ARRAY Function

In the non array version of the frequency function there is an additional step to take to achieve the same result as we have just seen for the ARRAY version of the FREQUENCY function. We set up the function differently too:

  • in cell H9 enter =FREQUENCY(temp,G9) and press Enter
  • now drag that down to fill the range H10:H17

Notice the difference between the two versions of the function. That is, the non array frequency function gives us the CUMULATIVE frequencies and to show the frequencies for each class interval we need to create the following … for demonstration only I have created an extra column but this column is all your need:

  • in cell J9 =FREQUENCY(temp,G9)
  • in cell J10 =FREQUENCY(temp,G10)-SUM(J$9:J9)
  • in cells J11:J17, copy down (fill) the formula in cell J10

You will now see this:

Screen Shot 2013-12-20 at 08.27.55

Screen Shot 2013-12-20 at 08.31.35


We can also use the COUNTIFS() function to find the frequency distribution of the temperatures too, as follows:

  • in cell K9 =COUNTIFS(temp,”>=0″,temp,”<10″)
  • in cell K10 =COUNTIFS(temp,”>=10″,temp,”<20″)
  • in cells K11:K17, copy down (fill) the formula in cell K10 and make sure you change the values of >= and < for each row

You will now see this:

Screen Shot 2013-12-20 at 21.04.13

Not the Same

Ah! Did you notice that the results are not all the same? Did you see that the results of the frequency formulas are a little different from the results of the COUNTIFS() formula? Look again at the final table above.

What has gone wrong? Nothing has gone wrong; except that I programmed the COUNTIFS() function a little tighter than then frequency function. I did this by saying in cell K9, for example, =COUNTIFS(temp,”>=0″ …) and in cell K10, as another example, =COUNTIFS(temp,”>=10″ …). Note the >= aspect of those formulas. In the frequency function formulas, though, I had the class boundaries set at 10, 20, 30 and didn’t say >= at any stage.

Because the temperature data are all recorded at just one decimal place, I felt safe in restating the class boundaries as:




But since that doesn’t look so good, I formatted cells G9:G17 to show no decimal places so it rounded the numbers to the nearest whole number: this is just for cosmetic effect.

I then changed the formulas in the class intervals column by rounding to zero decimal places for cosmetic effect again:

  • in cell F9 =0&” – “&ROUND(G9,0)
  • in cell F10 =ROUND(G9,0)&” – “&ROUND(G10,0)

and so on

Everything looks good now and everything is consistent too, so we can compare the two versions of frequency and COUNTIFS quite easily.


There you are, a little drawn out in some ways; but this demonstrates that Excel commonly provides more than one way to skin a cat!

I like to use array functions when I can because they contain the feature that not many people know about them so they find it very difficult to mess with them!

I like to demonstrate the non array frequency function for those of us who find array functions a little daunting.

I like to use the COUNTIFS() function because it is relatively new and it is very versatile and well worth learning to use in as many situations as possible.

Download the excel file by clicking here: frequency_func

2 Responses to “The Frequency and COUNTIFS Function: histograms”

  1. Per-Eric Says:

    Very helpful and nicely explained !
    But I can not get the copy down (fill) to work!
    any suggestions? using Excel 10


    in cell K9 =COUNTIFS(temp,”>=0″,temp,”=10″,temp,”<20″)
    in cells K11:K17, copy down (fill) the formula in cell K10.

    • Sorry for the delay in replying Per-Eric

      Did you download my Excel file for this? In case you did not, here are the formulas for K9 to K17, starting with K(

      K9 =COUNTIFS(temp,”>=0″,temp,”=10″,temp,”=20″,temp,”=30″,temp,”=40″,temp,”=50″,temp,”=60″,temp,”=70″,temp,”=80″,temp,”<90")

      Please let me know if this does not solve your problem


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: