3

I have seen the formula =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) in here

I am confused about this equal sign = ... What is it for ?

Community
  • 1
  • 1
Azamat Bagatov
  • 289
  • 3
  • 11
  • 26

2 Answers2

2

It is used for comparison.

If the range $A$2:$A2 is equal to A2, then it evaluates to TRUE.

It's similar if you type in a cell =2=2, you'll get TRUE as result, except the one in your question is comparing a range with a single cell.

Jerry
  • 70,495
  • 13
  • 100
  • 144
  • 3
    To add on to that, you'll notice that the row number on the end of the range is not locked. This means this particular formula was probably written to be dragged down, allowing the person to see exactly where the range stops being equal to the one cell. – PermaNoob Dec 27 '13 at 15:50
  • According to the syntax, SUMPRODUCT accepts array arguments , i.e. `SUMPRODUCT(array1 * array2)`. But `$A$2:$A2=A2` returns boolean. Why does it still work this way? – Azamat Bagatov Dec 27 '13 at 16:26
  • @AzamatBagatov The formula is likely intended to be dragged, and when that happens, you will end up with an array of boolean values. – Jerry Dec 27 '13 at 16:39
  • In Excel, Boolean results have a value (TRUE = 1 and FALSE = 0). This way they can be used in mathematical or logical expressions. – guitarthrower Dec 27 '13 at 17:23
  • @azamatbagatov The reason this works is because they are coercing the boolean results into an array. This is actually a very common way to use sumproduct(if not the one they intended) to compare something against multiple conditions. Now that we have the "ifs" formulas(`countifs`, `sumifs`, even `averageifs`), we have less of a need to do this since the "ifs" formulas allow multiple conditions. Before, though, you had to either do that with `sumproduct` or an array formula. Since so many people dislike array formulas, `sumproduct` became a popular way of doing this. – PermaNoob Dec 27 '13 at 17:53
0

Anchoring the top row of the range with $A$2 but allowing it to expand by not anching the row in the second cell reference allows to to have one instance where the sumproduct() will return only one value. If the full range was included in all cells, any value that occurred two or more times would have a 0 value returned.

Scott
  • 1