You have a sales report. We need to make a distribution graph, a histogram, like how many values are equal or less than five, how many values are higher than five but less or equal to ten and so forth.

Our sample data is very simple, that we could see the correct results immediately.

The sentence in E3 is =COUNTIFS($B$2:$B$11;”>”&D2;$B$2:$B$11;”<=”&D3) .

The COUNTIFS is used here with two criterias. The formula is counting how many values in the data range are higher than D2 and lower or equal to D3. Bit tricky part is define the cell reference “>”&D2.

Another option is to use FREQUENCY function.  

Enter the data array B2:B11 and bin arrays D3:D6. Then write the formula in E3 and hit enter.

You will get the frequencies per bin.

The third option is to use analysis toolpak. You need to download analysis toolpak via Excel options.

Press go.

Choose analysis toolpak and press ok.

After that, I have a data analysis button far right under data view.

Write again data array and bin arrays.

Press data analysis under data view.

Select histogram and press ok.

Enter input range, bin range and output options. Activate also the chart output. Press ok.

The result.

The COUNTIFS is more complex than FREQUENCY or data analysis. Data analysis is practical once it is loaded. Excel is offering various ways to calculate and present histogram.