For some reason, I'm not able to see the OP, but am reading this through
Robin's post.

As in all cases, no information is passed to the group when you just say
"not working"!

You described the return of the unary version as a zero total, but you
didn't describe your problem with the asterisk version, except that it "only
works sometimes".

What happens when it (asterisk version) doesn't work?

Your zero total using the unary version leads me to believe that the data in
your totaling range are *all* text values.

This is partially substantiated by the fact that the asterisk version works
"sometimes".

The asterisk version will total numeric and text values, as long as they
even look like numbers.
The unary version will bypass anything that's text, and simply not calculate
them, with no warning or error messages.

The asterisk version, even though it will calculate almost anything that
even looks like a number, will error out if there is any value in the
totaling column that doesn't look like a number.
This includes nulls ( "" , zero length strings) that may be the returns of
formulas in the totaling column,
or invisible characters that may have been imported with the numbers from a
web site (notably Char(160)).

SO, if you're "not working", in relation to the asterisk form means a
#Value! error, look in your totaling column for non-numeric text, or web
imported characters.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"Robin McLean" <Robin wrote in message
...


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