Hi ,

I will explain the following formula , assuming that it is the one you have used. If not , hopefully the explanation will help in understanding the other versions.

The formula is :

=INDEX(SlNo,MATCH(1,SIGN((COUNTIF($F$1:$F1,SlNo)<SUMIF(SlNo,SlNo,RepeatAmt))),0))

entered as an array formula , using CTRL SHIFT ENTER.

**SlNo** is a named range , which refers to the text strings that will be repeated ; in the example workbook , these strings are **Right** , **Left** , **North** , **East** , in the cells A2:A5.

**RepeatAmt** is a second named range , which refers to the numeric values which will used to define the number of times the above text strings will be repeated ; in the example workbook these values are 2 , 1 , 4 , 2 in the cells B2:B5.

With the above input data , the final output is supposed to be :

Right

Right

Left

North

North

North

North

East

East

The posted formula is not supposed to start in row 1 i.e. it cannot be entered in any cell in row 1 , since it refers to a cell in that row. It can be entered in any unused cell in row 2 downwards , assuming that the cells above it do not contain the text strings which have been entered in **SlNo**.

The outermost INDEX is simple enough to understand ; if we can generate a set of values to give us the following formula in the different cells in column F , then we will get what we want.

=INDEX(SlNo , **1**)

=INDEX(SlNo , **1**)

=INDEX(SlNo , **2**)

=INDEX(SlNo , **3**)

=INDEX(SlNo , **3**)

=INDEX(SlNo , **3**)

=INDEX(SlNo , **3**)

=INDEX(SlNo , **4**)

=INDEX(SlNo , **4**)

These highlighted values are being generated by the following part of the formula :

MATCH(1,SIGN((COUNTIF($F$1:$F1,SlNo)<SUMIF(SlNo,SlNo,RepeatAmt))),0)

The key functions here are the **SIGN** , **COUNTIF** and **SUMIF** functions.

The **SIGN** function takes in a numeric value , and returns one of the following 3 values :

-1 if the numeric value is negative

0 if the number is 0

1 if the number is positive

The **SUMIF** function generates an array of values corresponding to the repeat amounts for each text string.

You may ask why we use a SUMIF function to generate these values , when we already have the named range **RepeatAmt** which stores these values ; this will be clear when we consider a different situation later on.

The point is that the SUMIF function generates an array of values corresponding to the repeat amounts for each text string , and in the example , it will return the array {2;1;4;2}.

The **COUNTIF** function normally returns a single scalar value , which is a count of how many times a particular value occurs in a range ; the range is the first parameter , and the value whose occurrence in the range is to be counted is the second parameter.

However , if the second parameter is a range , instead of a single value , then the COUNTIF function returns an array of the same length. In the present case , because we are using :

COUNTIF($F$1:$F1,SlNo)

it will return an array with as many elements as the named range **SlNo** ; for the example data in the workbook , it will return an array of 4 elements.

For the first formula , since F1 does not contain a text string from **SlNo** , the return array will be :

{0;0;0;0}

When we compare this array with the array returned by the SUMIF function , we will have {0;0;0;0} < {2;1;4;2} , which will return an array :

{TRUE;TRUE;TRUE;TRUE}

The SIGN function converts this to numeric values , as follows :

{1;1;1;1}

The MATCH function then returns the scalar value 1 , since 1 has been found in the very first position.

The INDEX function now puts the text string **Right** in the first cell where the formula has been entered.

When we come to the second cell , the comparison now becomes :

{1;0;0;0} < {2;1;4;2}

which again returns the same array as before. This results in the second cell also having the text string **Right**.

In the third cell , the comparison becomes :

{2;0;0;0} < {2;1;4;2}

Now , the first element of the array on the left , 2 , is no longer less than the first element of the array on the right , 2.

Thus , the array returned by the above comparison becomes :

{FALSE;TRUE;TRUE;TRUE}

The SIGN function converts this to numeric values , as follows :

{0;1;1;1}

The MATCH function now returns 2 , and the INDEX function puts the text string **Left** in the third cell.

In the fourth cell , the comparison becomes :

{2;1;0;0} < {2;1;4;2}

Now , the first and second elements of the array on the left , 2 and 1 , are no longer less than the first and second elements of the array on the right , 2 and 1.

Thus , the array returned by the above comparison becomes :

{FALSE;FALSE;TRUE;TRUE}

The SIGN function converts this to numeric values , as follows :

{0;0;1;1}

The MATCH function now returns 3 , and the INDEX function puts the text string **North** in the fourth cell.

And so it goes till all the elements have been listed the given number of times.

And so we come to the question , why do we use the SUMIF function ?

Narayan