Determining Frequency Distribution in a Range of Cells

Last reviewed: November 4, 1994
Article ID: Q42801

2.x 3.00 4.00 4.00a 5.00 | 2.20 2.21 3.00

WINDOWS                  | OS/2

kbusage The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for OS/2 versions 2.2 and 3.0

The easiest way to determine the frequency of distribution in a range is to use the SUM and IF functions in an array. For example, to determine the number of 3's in the range B2:E10, use the following formula:

   =SUM(IF(B2:E10=3,1,0))

To enter this function as an array formula, press CTRL+SHIFT+ENTER. This will give you the desired result. The IF function, when entered as an array, checks each array element separately and returns either a 1 or a 0 (zero) depending on whether it compares true or false. For each 1 or 0, the SUM function keeps a total. After it goes through the entire selection, you will have the total number of 3's in the range.

KBCategory: kbusage

KBSubcategory:
Additional words: 5.00 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00 4.00a

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Microsoft KB Archive/42801

Determining Frequency Distribution in a Range of Cells