Hi Anhn,

Thanks for your reply.

Since it is straying 'off-topic' I will keep this as brief as I can whilst trying to clearly tie up the loose ends - since this will be my last post on this thread. I do so for the benefit of other members who will be reading this and may find it of interest! It is, after all, an extremely interesting, productive discussion and the sharing of information/bouncing around of ideas is exactly the purpose of the forums.

Regarding your testing.
By all means, use the VBA Timer to measure the time taken. The test results of the coercions in VBA are certainly off-topic - we had strayed into discussing data type coercions within formulas. Coercion in VBA itself is a completely different kettle of fish.

So let's test the calculation speed of the actual sumproduct formulas with the VBA timer....

With worksheet formulas that can be optimized by Excel, the speed of the below formulas are almost identical with =VALUE(...) is slightly slower of 1%.
Right, when coercing a single text/boolean data type into a numerical data type, the timing difference with the different methods of coercion is small. But, we're talking about arrays here. Typically a sumproduct formula could contain two or three arrays, each with 60,000+ elements. A worksheet might contain 10, 20 or even 100 of these formulas. So once you start coercing 180,000 elements per formula and there are say, a hundred of these formulas in the worksheet, the small timing difference of coercing each element in these arrays becomes extremely significant... tiny amount x 180,000 x 100 = A lot.

Here's some simple testing with the VBA timer. I timed the calculation speed of a worksheet with 20 of each formula and I took the average time of 200 tests.

Code:
Negation
=-SUMPRODUCT(-($A$2:$A$65536=$A$2:$A$65536),-($B$2:$B$65536=$B$2:$B$65536),-($C$2:$C$65536=$C$2:$C$65536))
Average time taken: 1.42984 seconds
Code:
Multiplication Syntax (Arg1)
=SUMPRODUCT(($A$2:$A$65536=$A$2:$A$65536)*($B$2:$B$65536=$B$2:$B$65536)*($C$2:$C$65536=$C$2:$C$65536))
Average time taken: 1.61729 seconds
Code:
Addition
=SUMPRODUCT(($A$2:$A$65536=$A$2:$A$65536)+0,($B$2:$B$65536=$B$2:$B$65536)+0,($C$2:$C$65536=$C$2:$C$65536)+0)
Average time taken: 1.83963 seconds
Code:
Multiplication Syntax (Arg1,Arg2)
=SUMPRODUCT(($A$2:$A$65536=$A$2:$A$65536)*1,($B$2:$B$65536=$B$2:$B$65536)*1,($C$2:$C$65536=$C$2:$C$65536)*1)
Average time taken: 1.84518 seconds
Code:
Exponentiation
=SUMPRODUCT(($A$2:$A$65536=$A$2:$A$65536)^1,($B$2:$B$65536=$B$2:$B$65536)^1,($C$2:$C$65536=$C$2:$C$65536)^1)
Average time taken: 2.84664 seconds
Of course, the actual average timing difference will vary for different users, but the ratios should be similar.

Perhaps (in this case with 20 formulas) saving a second each time a worksheet is calculated is insignificant for most people. In my industry, it is very significant. The bottom line is that they all give the correct answer, so, if one has reasoned that sumproduct is to be used in the first place, I can't see any reason to use anything but the fastest version since there is nothing else to separate them?

Stay with what you thought. Keep using -- if you love it.
It's not a question of love? I have explained the reasons why I use it - so of course I will continue to do so! If you 'love' using addition or multiplication, then carry on using them but please don't tell me that I - or others - should also because, as I have shown, there's no good reason to. It was you, after all, who first told me I should change the way I coerce data types [post #14], and not the other way round. I have been simply defending my methodolody...

Colin