SUMPRODUCT using data from multiple worksheets

F

Frank Kabel

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
Click to expand...
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.
 
D

Dale

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

Frank Kabel

Hi
do you have any error in this range?
-----Original Message-----
I get a #VALUE error. I know the formula could be
Click to expand...
shortened, but actually my intent is to add several other
criteria to be validated in the formula - if I can only
get the basic formula working.
 
F

Frank Kabel

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

Tom Ogilvy

('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))
 
Top