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:D61="Sam"),B54:B61)

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

Thanks in Advance

Hardeep Kanwar


--
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

"hardeep.kanwar" wrote:
What is the Difference Between "--" in First Function and "*" in
Second Function
1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D54:D61="Sam"),B54:B61)
2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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"),--(D54:D61="Sam"),B54:B61)

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

Thanks in Advance

Hardeep Kanwar


--
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

PS....

I wrote:
If any of B54:B62 is text

Obvious typo; should be B54:B61.


If any of B54:B62 is text, SUMPRODUCT(...*B54:B61)
will fail with a #VALUE error, whereas SUMPRODUCT(...,B54:B61) will not.

So I would write:

SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday")*( D54:D61="Sam"),B54:B61)

This avoids "--", which is superfluous in the case, but it separates B54:B61
to avoid any #VALUE error.


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

"JoeU2004" wrote in message
...
"hardeep.kanwar" wrote:
What is the Difference Between "--" in First Function and "*" in
Second Function
1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D54:D61="Sam"),B54:B61)
2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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"),--(D54:D61="Sam"),B54:B61)

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

Thanks in Advance

Hardeep Kanwar


--
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

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"),--(D54:D61="Sam"),B54:B61)

2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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"),--(D54:D61="Sam"),B54:B61)


2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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

"hardeep.kanwar" wrote:
1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D54:D61="Sam"),B54:B61)
2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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"),--(D54:D61="Sam"),B54:B61)

2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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"),--(D54:D61="Sam"),B54:B61)


2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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

There are some calculation timer procedures written by Charles Williams
posted in this article:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"hardeep.kanwar" wrote:
1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D54:D61="Sam"),B54:B61)
2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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"),--(D54:D61="Sam"),B54:B61)

2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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"),--(D54:D61="Sam"),B54:B61)


2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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

"T. Valko" wrote:
There are some calculation timer procedures written by Charles Williams
posted in this article:
http://msdn2.microsoft.com/en-us/library/aa730921.aspx

I am aware of them. And if you look carefully, you will see that we use
similar techniques.


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

"T. Valko" wrote in message
...
There are some calculation timer procedures written by Charles Williams
posted in this article:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

--
Biff
Microsoft Excel MVP


"JoeU2004" wrote in message
...
"hardeep.kanwar" wrote:
1st:SUMPRODUCT(--(A54:A61="Orange"),--(C54:C61="Friday"),--(D54:D61="Sam"),B54:B61)
2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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"),--(D54:D61="Sam"),B54:B61)

2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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"),--(D54:D61="Sam"),B54:B61)


2nd:SUMPRODUCT((A54:A61="Orange")*(C54:C61="Friday ")*(D54:D61="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