Taking a different approach which leaves the original data intact, without reordering, filtering or copying, 3 helper columns are added to the data list as shown next.

Column *Row* is a simple index series starting at 1, column *Person2* is the same as *Person* but uses an IF function to insert a null string in rows with zero *Sales*. *MCount* provides a count, by *Person*, of months with non-zero *Sales*.

*MCount* interacts with a results list which contains a row for each sales person, as shown next.

In the results list, column *Months* simply counts the number of months with non-zero sales in the data list for each sales person. The formula in cell I2 is

`=COUNTIFS(A$2:A$21,H2,$C$2:$C$21,">0")`

*RowM1* shows the *Row* value in the data list corresponding to the first month with non-zero sales for each sales person. The formula in cell J2 is

`=MATCH(H2,$E$2:$E$21,0)`

Columns *Month1*, *Month2* and *Month3* are the month numbers within column *MCount* (of the data list) corresponding to the first, second and third months used for calculating the 3-month average, whilst *Sales1*, *Sales2* and *Sales3* are the sales values for these 3 months. *Average* is the calculated 3 month average.

The formulae for cells K2, L2 and M2 are respectively

`=I2-1`

`=K2+1`

`=L2+1`

The formula in cell N2 is

`=SUMIFS($C$2:$C$21,$E$2:$E$21,$H2,$F$2:$F$21,K2)`

and relies on the values in the *MCount* column of the data list (range `$F$2:$F$21`

, see next). Cell N2 is copied to cells O2 and P2.

The range I2:Q2 can be copied to all subsequent rows of the results list.

**Data list - column ***MCount*

The formula in cell F2 is

`=IF(C2>0,1,0)`

This puts 0 or 1 into the cell. 0 indicates that the person in cell E2 had no sales in the month shown in cell `B2`

, whilst 1 indicates that it was the first month of sales for this person.

The formula in cell F3 is considerably more complex and is

`=IF(LEN(E3)=0,0,IF(VLOOKUP(E3,H$2:J$3,3,FALSE)=D3,1,1+MAXIFS(F$2:F2,E$2:E2,E3)))`

The TRUE part of the first IF simply delivers 0 if the row corresponds to *Sales* of zero.

Otherwise, *Sales* are non-zero and the second IF is triggered. In this second IF the *Person* in cell E3 is looked up in the results table and, if the *RowM1* value (row number in data list corresponding to first month of sales) matches the *Row* value in cell D3, this means that row 3 corresponds to the first row with non-zero sales for the person identified in cell E3.

If the *RowM1* and *Row* values differ, row 3 in the data table corresponds to a subsequent month of sales (after the first) for the person in cell E3. In this case, the number of the month is obtained by adding 1 to the maximum value in column F for the sales person in cell E3 in the preceding rows of the data list. A MAXIFS function is used to determine this maximum.

The formula in cell F3 is copied to subsequent rows of the data list. The appropriate use of relative and absolute addressing in the formula ensures that the MAXIFS function uses the appropriate ranges of cells in the copies.

**Notes**

- The use of MAXIFS requires Excel 2019 or later
- It is assumed that (i) data is in chronological order and (ii) there is, at most, one non-zero row of sales data for each sales person and month combination.
- In the images, a blue font indicates a formula and a black font a static value.