formula to find unique instances in powerpivot

[stephme55]

Hello. Someone gave me this formula to find unique instances in two columns (so unique combinations). How does it work? Thanks.

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

[Richard Buttrey]

Hi,

I'm not entirely sure it is giving you unique occurencies. But that depends on what you mean by unique and in what range. For instance with the following pairs of numbers

1,2
1,3
1,4

in A2:B4 the formula returns a value of 1 for each row. Whilst that's true for the first range A$2:A2 & B$2:B2 of course that single pair is unique but on the second row there are self evidently two unique pairs. Assuming the formula is in C2 maybe it should be

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