A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SumProduct Query



 
 
Thread Tools Display Modes
  #1  
Old June 14th, 2009, 08:23 AM posted to microsoft.public.excel.worksheet.functions
hardeep.kanwar[_9_]
external usenet poster
 
Posts: 1
Default SumProduct Query


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: http://www.thecodecage.com/forumz/member.php?userid=170
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106459

  #2  
Old June 14th, 2009, 09:55 AM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 1,748
Default SumProduct Query

"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:
http://www.thecodecage.com/forumz/member.php?userid=170
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=106459


  #3  
Old June 14th, 2009, 10:04 AM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 1,748
Default SumProduct Query

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")*( D5461="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"),--(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:
http://www.thecodecage.com/forumz/member.php?userid=170
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=106459



  #4  
Old June 14th, 2009, 02:39 PM posted to microsoft.public.excel.worksheet.functions
hardeep.kanwar[_10_]
external usenet poster
 
Posts: 1
Default SumProduct Query


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

  #5  
Old June 14th, 2009, 08:09 PM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 1,748
Default SumProduct Query

"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


  #6  
Old June 14th, 2009, 09:34 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default SumProduct Query

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"),--(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




  #7  
Old June 14th, 2009, 09:57 PM posted to microsoft.public.excel.worksheet.functions
joeu2004
external usenet poster
 
Posts: 1,748
Default SumProduct Query

"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"),--(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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.