hardeep.kanwar said:
1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D54
61="Sam"),B54:B61)
2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday")*(D54
61="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 said:
JoeU2004;380596 Wrote:
What is the Difference Between "--" in First Function and "*" in
Second Function
1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D54
61="Sam"),B54:B61)
2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday")*(D54
61="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 -----
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"),--(D54
61="Sam"),B54:B61)
2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday")*(D54
61="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/showthread.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/showthread.php?t=106459
