Counting consecutive values

Let's use this analogy for the FREQUENCY function.

We have penquins to ship.
We have boxes that hold various numbers of penguins.

For each penguin order, we need to reserve a box of the appropriate size.

Using your posted workbook and using Row_13 for our example...
each column number represents a number of penguins to ship

These are the values in columns D:M

Please Login or Register  to view this content.

We have orders for:

Please Login or Register  to view this content.

The first section of the FREQUENCY function: IF(D13:M13=1,COLUMN(D:M))
shows those numbers when evaluated: {FALSE,FALSE,6,7,8,FALSE,10,FALSE,FALSE,13}

The second section effectively tells us what box sizes we have to work with:
IF(D13:M13<>1,COLUMN(D:M))
resolves to: {4,5,FALSE,FALSE,FALSE,9,FALSE,11,12,FALSE}

So for box capacities, we have:
4
5
9
11
12
Over_12

If we don't have an exact match for a penguin order, we go to the next size up.

The FREQUENCY function tells us which box capacities are appropriate for each penguin order.
Then it calculates the count for each box capacity

Please Login or Register  to view this content.

These are the boxes required:

Please Login or Register  to view this content.

We want to know the box capacity we need the most of...
So...our B13 formula resolves this way:

Please Login or Register  to view this content.

Does that help?

that's it i'm off to north pole ! no penguins there lol

OK...but, today all I can think of is aardvarks!

I'll call the FREQUENCY function advanced, due to the learning curve.

The FREQUENCY function uses the bins in the same way a an Amazon.com book
packer would select a box. If you can't find one the exact size, you go one size bigger
(the book certainly won't fit in a smaller box, right?) Then it calculates how many of each
bin (box) are required to accommodate the data (books).

Here's how I became comfortable with it. One day, I spent an hour playing with various
input cells and bin cells. At some point, it all made sense. Now, it's fairly easy to use.
The biggest gotcha in learning that formula is realizing that it will sometimes create a
bin for items smaller than your minimum bin and/or a create a bin for items larger than your
maximum bin...depending on the data. Built carefully, the function can be very powerful.

I hope that helps.