The SUMPRODUCT provides very similar results (I compared to the countif). The major difference is that the value countif 'counted' 3170 times is only counted once with the sumproduct.
However, the numbers still don't match what the pivot table provides, for example. I still get a higher number than the total # of rows in the original data set.

Quote Originally Posted by daddylonglegs View Post
Try using SUMPRODUCT like this

=SUMPRODUCT(('sheet with original data'!$B$2:$B$28124='sheet after removing duplicates'!B2)+0)

SUMPRODUCT is less efficient than COUNTIF but it has fewer inconsistencies - do you get the same results?