"hardeep.kanwar" wrote:
1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61)
2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61))
[....]
Which is the best one

I do not measure any significant performance difference using a range of
60000 cells.

As I noted previously, ",B54:B61" is better than "*B54:B61" if you expect
that some cells have text (e.g. "").

The macro below demonstrates the measurement procedure. Note that the
SUMPRODUCT formula is assumed to be in A1.


Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal msec As Long)
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (ByRef
clkFreq As Currency) As Boolean
Private Declare Function QueryPerformanceCounter Lib "kernel32" (ByRef
clkCount As Currency) As Boolean

Sub doit()
Const n As Integer = 30
Dim freq As Currency, st As Currency, et As Currency, ct As Currency
Dim mint As Currency, maxt As Currency
Dim i As Integer, x As Long, calcSave, iterSave
Dim rng As Range

calcSave = Application.Calculation
iterSave = Application.Iteration
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.Iteration = False

Set rng = Range("a1")

x = QueryPerformanceFrequency(freq)

Debug.Print "-----"

mint = 999999999
For i = 0 To n
Call Sleep(30) 'new sched quantum for each iteration
x = QueryPerformanceCounter(st)
rng.Calculate
x = QueryPerformanceCounter(et)
If i 0 Then 'skip first time, affected by VBA
overhead
et = et - st 'elapased time
ct = ct + et 'cumulative elapsed time for average
If et maxt Then maxt = et
If et mint Then mint = et
End If
Next i

' do ctrl-G to see results
Debug.Print Format(ct / freq / n * 1000, "0.000000 msec");
Debug.Print ", min "; Format(mint / freq * 1000, "0.000000");
Debug.Print ", max "; Format(maxt / freq * 1000, "0.000000")

Application.Iteration = iterSave
Application.Calculation = calcSave
Application.ScreenUpdating = True
End Sub


----- original message -----

"hardeep.kanwar" wrote in message
...

JoeU2004;380596 Wrote:
"hardeep.kanwar" wrote:
What is the Difference Between "--" in First Function and "*" in
Second Function

1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61)

2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61))

There is no logical difference. "--" (double-negation) is merely a way
to
convert boolean results (TRUE, FALSE) into a number, which SUMPRODUCT
requires. But any arithmetic operation will do the same. Moreover,
SUMPRODUCT(x,y,z) is logically the same as SUMPRODUCT(x*y*z).

However, there is an operational difference that may or may not
matter,
depending on the situation. If any of B54:B62 is text,
SUMPRODUCT(...*B54:B61) will fail with a #VALUE error, whereas
SUMPRODUCT(...,B54:B61) will not. The reason is that SUMPRODUCT is
designed
to tolerate text, whereas arithmetic expressions do not.


----- original message -----

"hardeep.kanwar" wrote in
message
...

Hi! Experts

What is the Difference Between these Sumproduct Function

It show the Same Results

What is the Difference Between "--" in First Function and "*" in
Second Function


1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D5461="Sam"),B54:B61)


2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D5461="Sam")*B54:B61))

Thanks in Advance

Hardeep Kanwar


--
hardeep.kanwar

------------------------------------------------------------------------
hardeep.kanwar's Profile:
'The Code Cage Forums - View Profile: hardeep.kanwar'
(http://www.thecodecage.com/forumz/member.php?userid=170)
View this thread:
'SumProduct Query - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=106459)


Thanks sir

Its Really Help Full for me

Which is the best one


--
hardeep.kanwar
------------------------------------------------------------------------
hardeep.kanwar's Profile:
http://www.thecodecage.com/forumz/member.php?userid=170
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=106459