Clever Geek Handbook

How does the SUMPRODUCT command work in this example?

The following code allows me to define different values ​​in a pivot table in Excel:

=SUMPRODUCT(($A$A:$A2=A2)*($B$2:$B2=B2))

See also: Simple pivot table for counting unique values

The code works fine. However, can anyone help me understand how this code actually works?

+3


source to share


2 answers


You write: The following code allows me to define different values ​​in a pivot table in Excel

Not. Only this formula does not. Read on to explain what it does.

There is a typo in the formula. It should be

=SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))

See the difference?

The formula starts on line 2 and is copied down. In each line, reference $ A $ 2 and reference $ B $ 2 will remain the same. The $ signs make them absolute links. The relative references $ A2 and A2 will change by line numbers when copied, so on line 3 A2 will change to A3 and B2 will change to B3. On the next line, these will be A4 and B4, etc.

You might want to create a sample script with data similar to the data in the stream you are linking to. Then use the Evaluate Formula tool on the Formulas ribbon to see step by step what is calculated. The formula is evaluated internally. Suppose the formula has been copied to line 5 and now we are looking at

=SUMPRODUCT(($A$2:$A5=A5)*($B$2:$B5=B5))

($A$2:$A5=A5)

this bit compares all cells from A2 to A5 with the value in A5. The result is an array of four values: true or false. The next bit ($B$2:$B5=B5)

also returns an array of true or false values.

enter image description here

These two arrays are multiplied and the result is an array of 1 or 0 values. Each array has the same number of values.



The first value of the first array will be multiplied by the first value of the second array. (see red arrows)

The second value of the first array will be multiplied by the second value of the second array. (see blue arrows)

etc.

True * True will return 1, everything else will return 0. The result of the multiplication:

enter image description here

The nature of the SumProduct function is to sum the result of multiplications (product), so that's what it does.

This function by itself does nothing to set different values ​​in Excel. In the thread you are linking to, Sumproduct is wrapped in an IF statement and THAT is where the different values ​​are identified.

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

In simple words: If the combination of the value in column A of the current row and column B of the current row has already appeared above, return zero, otherwise return 1.

This marks the different meanings of the combined columns A and B.

+3


source


Freitz, I think you made a type here, since the formula should be:

=SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))

Let's break it down into 2 parts:



  • First we check the cells between A2

    and A2

    , so only one cell, and we check the number of cells that are equal A2

    . In this case, the output should be 1 as you are comparing A2

    with A2

    . However, you cannot compare A2

    with A2

    . If you selected 2 cells to be equal A2

    , the results would be 2. You can compare as many cells as you like with A2 (replace the characters after $ to modulate). We do the same for the second parenthesis, except that the rotation value B2

    .
  • After that, you need to understand what the SUMPRODUCT function does. It sums the product value for the range of the array. For example, let's say you have a value of 1 on A1

    , 1 on A2

    , 2 on B1

    and 3 on B2

    , if you do SUMPRODUCT((A1:A2)*(B1:B2))

    , you get (1*2) + (1*3) = 5

    . So in the example you gave us, it will give the sum (A2=A2)*(B2=B2) = 1

    .

So it will output the amount of the pair (Ax, Bx), which is (A2, B2). From the link, you can see that if you select only the first line, the function will output 1 (and therefore will output 1 IF

), but if you select the first 2 lines, the function will output 2, (and therefore IF

will output 0).

Hopefully this made sense to you as I hoped I was not mistaken in the explanation.

+3


source






More articles:


All Articles