Hi Steve

Glad to hear that the explanations and formulae I sent you back having
seen your workbook solved your problems.

You don't say what problems you are now getting. What is the error that
is being returned?
Are you talking about your Acre-feet column like column D in the
workbook you sent me, with formulae like =C2*200/50 ?
On your workbook, that was accurately being calculated in column J of
sheet Sum.

If it is not working in another workbook, then there must be something
wrong with one or more of the values in column B, giving rise to an
error in the calculated result for column C.
It will not be an error with Sumproduct, but an error with the source
data that needs correcting.

--
Regards

Roger Govier


"SteveDB1" wrote in message
...

Hi all.
After intense discussion, and "debate" I'll consider this post
answered for
now.
I have in fact tried all of Harlan's, and Roger's recommended
solutions, and
have come up with what appears to be a viable means of solving the
problem.
And as such has already been implimented in updating workbooks.
However, one of my colleagues has encountered an issue that I've not
encountered, and in fact, it has never been an issue for me.
He's found that all works except when he uses a formula in the final
source
column-- i.e., the last "array" of the sumproduct, in our case:
=sumproduct ((...)*(...)*(Map!$C$3:$C$n))
Where 'n' is the end of the row range, and the formula that I'm
referencing
in the C column is of the general form- (=B2*x/y)-- ignore the
paren's; x,
and y are some predetermined values-- you can pick any arbitrary
numbers.
I quite frankly can't think of any reason why it would not work, as
mine has
always worked for this portion, and never once been an issue. My
issues, it
turns out were cross-datatypes that were returned as false, when the
values
represented should've returned true.
So, again-- thank you for everyone's assistance.
Hope all have a great T-day-- for those on this side of the "pond".
For
those overseas... enjoy the rest of your week.
See you all next time I need help.

"SteveDB1" wrote:

Hi folks.
I use sumproduct to sum columns of values after testing against 2
criteria.
At first it was working great, and then it stopped working once one
cell
that I was testing against had a value in it. It's actually become
quite
finicky. It'd work in some cases, but not in others.
My version of sumproduct is:
=sumproduct((Col&RowRange = cell a)*(Col&RowRange = cell
b)*(Col&RowRangeFinal))
Where Col&RowRange would be a column, and range of rows; cell a, and
cell b
would be the values that I'm looking for; and the Col&RowRangeFinal
would be
the column that I'm summing up.

I have to ask-- would this be affected by cell formatting? (I've gone
so far
as to copy and paste cell values from the source regions to the final
regions, and it still only works sometimes.) If so, why? It'd be
really nice
to know, so I can ensure consistency, and continuity.
what else would cause this to fail?
Oh, and I did try the comma's instead of *'s as shown in the office
assistant help file. That killed everything with no sign of errors,
or #ref,
value, etc....-- i.e., I just got 0.000 for the returned value.

Thus far, aside from this issue, it's been a real godsend, so I'd
hate to be
forced to use something else to solve my reasons for using it to
begin with.