SUMPRODUCT function

[Ufoo]

Hello Chandoo, hello excel users. I am Ufoo from Tanzania, East Africa. I have a problem, which is based on Chandoo's technique of finding the nth value. Someone please unravel the formulas in the image below. The original excel sheet from chandoo.org is attacehd with this mail. Thanks:

 

[NARAYANK991]

Hi ,

The image itself is not available ; can you post the formula which needs explanation ?

Narayan

 

[Ufoo]

Hi ,

The image itself is not available ; can you post the formula which needs explanation ?

Narayan

Hello, the questions and the formulas:
1) What is the net sale amount for the last occurrence of Jamie: =SUMPRODUCT((MAX((C5:C17="Jamie")*ROW(C5:C17))=(C5:C17="Jamie")*ROW(C5:C17))*1,E5:E17)
2) Is there a way of getting the second sales of Josh without using the helper column?: =INDEX($E$5:$E$17,SUMPRODUCT(SMALL(($C$5:$C$17="Josh")*(ROW($C$5:$C$17)-4),2+COUNTIF($C$5:$C$17,"<>Josh")))).

Let me attach the excel sheet in question.

 

[NARAYANK991]

Hi ,

Thank you for posting both the question and the formula ; I am giving my explanation of the formula without having gone through the worksheet. Let me know if this explanation does not address your doubts.

1. What is the net sale amount for the last occurrence of Jamie ?

The names occur in the range C5:C17 , while the sale amounts occur in the range E5:E17.

We need to find out which row in this range has the last occurrence of Jamie ; I am assuming that the word last means the occurrence which occurs last within the range by way of physical location i.e. suppose Jamie occurs in rows 7 , 9 and 16 ; then the last occurrence of Jamie will be the one in C16.

Finding out the last row where Jamie occurs is done by taking the maximum value of all the rows where Jamie occurs.

Locating all the rows where Jamie occurs is done by this portion of the formula :

(C5:C17="Jamie")

This will return an array of TRUE / FALSE values , TRUE where Jamie occurs , and FALSE where Jamie does not occur.

Assuming as before , that Jamie occurs in rows 7 , 9 and 16 , the resulting array will be :

{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; TRUE;FALSE}

What we now need is the values 7 , 9 and 16 , so that we can get at the maximum of these values i.e. 16.

If we multiply the above array of TRUE / FALSE values by the row numbers where they occur , we will get :

{0;0;7;0;9;0;0;0;0;0;0;16;0}

This is because TRUE is equivalent to 1 and FALSE is equivalent to 0 , and performing an arithmetic operation , such as multiplication , on a boolean array converts the TRUE / FALSE values to their numeric equivalents.

The row numbers themselves form an array such as :

{5;6;7;8;9;10;11;12;13;14;15;16;17}

Multiplying the two arrays :

{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; TRUE;FALSE}

and

{5;6;7;8;9;10;11;12;13;14;15;16;17}

results in the array :

{0;0;7;0;9;0;0;0;0;0;0;16;0}

Taking the maximum of the above array will give us 16.

Next we test where this maximum occurs by :

(MAX((C5:C17="Jamie")*ROW(C5:C17))=(C5:C17="Jamie")*ROW(C5:C17)

which will result in an array of TRUE / FALSE values ; of course , there should only be one TRUE value where the maximum occurs , and so the resulting array should be :

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; TRUE;FALSE}

The overall result is got by using the SUMPRODUCT function , which returns the sales amount corresponding to the TRUE value in the above array.

=SUMPRODUCT((MAX((C5:C17="Jamie")*ROW(C5:C17))=(C5:C17="Jamie")*ROW(C5:C17))*1,E5:E17)

Narayan

 

[Ufoo]

Hi ,

Thank you for posting both the question and the formula ; I am giving my explanation of the formula without having gone through the worksheet. Let me know if this explanation does not address your doubts.

1. What is the net sale amount for the last occurrence of Jamie ?

The names occur in the range C5:C17 , while the sale amounts occur in the range E5:E17.

We need to find out which row in this range has the last occurrence of Jamie ; I am assuming that the word last means the occurrence which occurs last within the range by way of physical location i.e. suppose Jamie occurs in rows 7 , 9 and 16 ; then the last occurrence of Jamie will be the one in C16.

Finding out the last row where Jamie occurs is done by taking the maximum value of all the rows where Jamie occurs.

Locating all the rows where Jamie occurs is done by this portion of the formula :

(C5:C17="Jamie")

This will return an array of TRUE / FALSE values , TRUE where Jamie occurs , and FALSE where Jamie does not occur.

Assuming as before , that Jamie occurs in rows 7 , 9 and 16 , the resulting array will be :

{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; TRUE;FALSE}

What we now need is the values 7 , 9 and 16 , so that we can get at the maximum of these values i.e. 16.

If we multiply the above array of TRUE / FALSE values by the row numbers where they occur , we will get :

{0;0;7;0;9;0;0;0;0;0;0;16;0}

This is because TRUE is equivalent to 1 and FALSE is equivalent to 0 , and performing an arithmetic operation , such as multiplication , on a boolean array converts the TRUE / FALSE values to their numeric equivalents.

The row numbers themselves form an array such as :

{5;6;7;8;9;10;11;12;13;14;15;16;17}

Multiplying the two arrays :

{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; TRUE;FALSE}

and

{5;6;7;8;9;10;11;12;13;14;15;16;17}

results in the array :

{0;0;7;0;9;0;0;0;0;0;0;16;0}

Taking the maximum of the above array will give us 16.

Next we test where this maximum occurs by :

(MAX((C5:C17="Jamie")*ROW(C5:C17))=(C5:C17="Jamie")*ROW(C5:C17)

which will result in an array of TRUE / FALSE values ; of course , there should only be one TRUE value where the maximum occurs , and so the resulting array should be :

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE; TRUE;FALSE}

The overall result is got by using the SUMPRODUCT function , which returns the sales amount corresponding to the TRUE value in the above array.

=SUMPRODUCT((MAX((C5:C17="Jamie")*ROW(C5:C17))=(C5:C17="Jamie")*ROW(C5:C17))*1,E5:E17)

Narayan

Thanks a lot. This is very helpful. I believe you are mulling over the second question.