Hi
the formula looks o.k.. What kind of error do you get?.

Note: you can shorten this formula to:
=SUMPRODUCT(--('mkr Calc Data'!F1:F1000=1))

or
=COUNTIF('mkr Calc Data'!F1:F1000,1)


-----Original Message-----
I am trying to use the SUMPRODUCT function to analyze
data in a second worksheet called "mkr Calc Data. However,
it seems that I cannot use references to a seperate
worksheet - I even tried using named ranges.
Here is the calc I am using.
=SUMPRODUCT(('mkr Calc Data'!F1:F1000=1)*('mkr Calc Data'!
F1:F1000))

Help.
.

Frank,
You've helped me out with a similar problem recently and
the equation you proposed worked fine. I'm not familiar
with the function of the "--" when used in a formula. I've
searched in excel and can't find any reference to it's
use. Would you mind posting a brief explaination?
-----Original Message-----
Hi
the formula looks o.k.. What kind of error do you get?.

Note: you can shorten this formula to:
=SUMPRODUCT(--('mkr Calc Data'!F1:F1000=1))

or
=COUNTIF('mkr Calc Data'!F1:F1000,1)


-----Original Message-----
I am trying to use the SUMPRODUCT function to analyze
data in a second worksheet called "mkr Calc Data.
However,
it seems that I cannot use references to a seperate
worksheet - I even tried using named ranges.
Here is the calc I am using.
=SUMPRODUCT(('mkr Calc Data'!F1:F1000=1)*('mkr Calc
Data'!
F1:F1000))

Help.
.

.

Hi
the double minus (unaray operator) coerces the boolean
values (TRUE/FALSE) to numbers (1/0)

-----Original Message-----
Frank,
You've helped me out with a similar problem recently and
the equation you proposed worked fine. I'm not familiar
with the function of the "--" when used in a formula.
I've
searched in excel and can't find any reference to it's
use. Would you mind posting a brief explaination?
-----Original Message-----
Hi
the formula looks o.k.. What kind of error do you get?.

Note: you can shorten this formula to:
=SUMPRODUCT(--('mkr Calc Data'!F1:F1000=1))

or
=COUNTIF('mkr Calc Data'!F1:F1000,1)


-----Original Message-----
I am trying to use the SUMPRODUCT function to analyze
data in a second worksheet called "mkr Calc Data.
However,
it seems that I cannot use references to a seperate
worksheet - I even tried using named ranges.
Here is the calc I am using.
=SUMPRODUCT(('mkr Calc Data'!F1:F1000=1)*('mkr Calc
Data'!
F1:F1000))

Help.
.

.

.

('mkr Calc Data'!F1:F1000=1)
Returns an array of true false (boolean) values.

using -- converts these to an array of 0 and 1's so they can be added.

(using just one would make them 0 and -1) so you could do

=-Sumproduct(-('mkr Calc Data'!F1:F1000=1))

--
Regards,
Tom Ogilvy

"Dale" wrote in message
...
Frank,
You've helped me out with a similar problem recently and
the equation you proposed worked fine. I'm not familiar
with the function of the "--" when used in a formula. I've
searched in excel and can't find any reference to it's
use. Would you mind posting a brief explaination?
-----Original Message-----
Hi
the formula looks o.k.. What kind of error do you get?.

Note: you can shorten this formula to:
=SUMPRODUCT(--('mkr Calc Data'!F1:F1000=1))

or
=COUNTIF('mkr Calc Data'!F1:F1000,1)


-----Original Message-----
I am trying to use the SUMPRODUCT function to analyze
data in a second worksheet called "mkr Calc Data.
However,
it seems that I cannot use references to a seperate
worksheet - I even tried using named ranges.
Here is the calc I am using.
=SUMPRODUCT(('mkr Calc Data'!F1:F1000=1)*('mkr Calc
Data'!
F1:F1000))

Help.
.

.

Dale,

There is a detailed explanation at

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Multiple Condition Tests (using SUMPRODUCT)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dale" wrote in message
...
Frank,
You've helped me out with a similar problem recently and
the equation you proposed worked fine. I'm not familiar
with the function of the "--" when used in a formula. I've
searched in excel and can't find any reference to it's
use. Would you mind posting a brief explaination?
-----Original Message-----
Hi
the formula looks o.k.. What kind of error do you get?.

Note: you can shorten this formula to:
=SUMPRODUCT(--('mkr Calc Data'!F1:F1000=1))

or
=COUNTIF('mkr Calc Data'!F1:F1000,1)


-----Original Message-----
I am trying to use the SUMPRODUCT function to analyze
data in a second worksheet called "mkr Calc Data.
However,
it seems that I cannot use references to a seperate
worksheet - I even tried using named ranges.
Here is the calc I am using.
=SUMPRODUCT(('mkr Calc Data'!F1:F1000=1)*('mkr Calc
Data'!
F1:F1000))

Help.
.

.