Exceljet

Quick, clean, and to the point

Count consecutive monthly orders

Excel formula: Count consecutive monthly orders
Generic formula 
{=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng))))}
Explanation 

To count consecutive monthly orders, you can use an array formula based on the FREQUENCY function, with help from COLUMN and MAX.

In the example shown, the formula in I5 is:

{=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5))))}

Note: this is an array formula and must be entered with Control + Shift + Enter.

How this formula works

This is a tricky formula to understand, so buckle up!

They key to the formula is knowing that FREQUENCY gathers numbers into "bins" in a specific way. Each bin represents an upper limit, and generates a count of all numbers in the data set that are less than or equal to the upper limit, and greater than the previous bin number. The trick then is to create the data_array with using the condition you want to test for (order count greater than zero in this case), and the bins_array using the opposite condition.

To create the data_array bin we use the following:

IF(C5:H5>0,COLUMN(C5:H5))

We test order count in each month and, if positive, return the column number where order count > 0. The resulting array looks like this:

{3,FALSE,FALSE,6,7,8}

Notice that only columns where order count > 0 make it into this array.

The bins array is generated with this:

IF(C5:H5=0,COLUMN(C5:H5))

This puts column numbers for order counts = 0 into an array which ends up like this:

{FALSE,4,5,FALSE,FALSE,FALSE}

Only columns where order count = 0 make it into this array where, per standard FREQUENCY behavior, they become the functional bins that tally non-zero orders. Wins are translated to FALSE and don't collect any numbers from the data array, since FALSE values are ignored.

With data array and bin arrays above, frequency returns an array of counts per bin in an array like this:

{1;0;3}

Now we simply wrap the MAX function around the array returned by FREQUENCY. MAX then returns the highest number in the array as the final result. 

Other consecutive values

To count consecutive occurrences of other values, just adjust the logic as needed following the same pattern: the first condition tests for the thing you want to count, the second condition tests for the opposite.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.