Here, data_array is the data in the range C2:C13, and the bins_array is the range C18:C25. The topic explains that because FREQUENCY returns an array, you would need to select the range D18:D25, type in...
...and then press Ctrl+Shift+Enter, to enter the formula as a multi-cell array.
The problem with this approach is that you can't modify the number of bins in your distribution easily. To illustrate, if you select row 21 and then try to insert (Ctrl++) or delete (Ctrl+-) a row, Excel launches an error dialog that says, "You can not change part of an array."
However, we can use a different approach with the FREQUENCY function.
2. Excel's FREQUENCY Function, with INDEX
We know that the FREQUENCY function returns an array. But consider the syntax for the INDEX function:
That is, INDEX accepts arrays. To illustrate, this formula...
...entered normally (not array-entered) returns the value 3. This corresponds with the value in cell D23 in the results table repeated here.
Therefore, we can enter this formula in the cell shown...
...and copy the formula down the column as needed. (You enter the formula in one line, of course.)
The value of this approach is that if you want to adjust your distribution of bins by
inserting or deleting rows, you can do so easily.
3. Excel's SUM-IF Array Formulas
After I first set up the counts using method 1, I wanted to find a quick way to check the results. The SUM-IF approach came immediately to mind.
To use this approach in the figure above, array-enter these formulas in the cells shown:
The first test checks whether the source Value contains an empty cell. For each cell that's not empty, the test returns TRUE, otherwise it returns FALSE. The second test returns TRUE when values in the source data are less than or equal to the amount in cell C17. Multiplying the tests returns an array of TRUEs and FALSEs that is TRUE whenever both test results are TRUE.
The rest of the IF function returns an array of 1s and 0s; and the SUM function returns the sum of these results.
Be sure to array-enter this formula. That is, with only cell D17 selected, type in the formula shown and then array-enter the formula by pressing Ctrl+Shift+Enter. Follow the same approach for the two formulas that follow.
When the two test results are multiplied in this array formula, we get an array with TRUE whenever numbers in the VALUE range need to be counted, the IF replaces the TRUE values with 1s, and the SUM returns the sum of those 1s. After you array-enter this formula, copy it to the range D19 through D23.
Array enter this simple formula.
4. Excel's SUMPRODUCT Function
Excel's SUMPRODUCT function works much like the SUM-IF approach. But it doesn't need to be array entered when you use the syntax I'll demonstrate. (SUMPRODUCT does need to be array-entered if you use other syntax, however.)
To use this approach, enter the following formulas in the cells shown:
At this point only the formula in cell D24 needs explanation. The test in this formula returns TRUE and FALSE, not 1 and 0. Before the results can be summed, they need to be convereted to 1 and 0. By adding zero to those results, Excel converts TRUE and FALSE to 1 and 0, as required. We would get the same result if we multiplied the test by 1, using this formula:
As with the SUM-IF approach, the first and the last formulas need to be different from
the interior formulas. Copy cell D18 down the column to cell D23.
5. Excel's COUNTIFS Function
New Excel (Excel 2007) adds a fifth way to find the same results, the COUNTIFS function.
Classic Excel offers the SUMIF and COUNTIF functions, which accept only one test criteria. But the SUMIFS and COUNTIFS functions accept any number of criteria. They use this syntax:
To use the COUNTIFS function in New Excel, enter these formulas for the cells shown:
As before, copy cell D18 down the column through cell D23.
Which Version Should You Use?
There's no good answer to this question. Each version has advantages over the other versions. Each version can be used in circumstances where the other versions probably wouldn't be recommended.
However, if you're somewhat familiar with them all, you'll recognize what's happening when you read workbooks created by others. And you'll have alternatives when you're not able to make one approach work.