Ok. upon further investigation, and various "trials" I've found that by
"forcing" a solution, by means of inputting a value, such as =0, or
=1*cell_contents I can obtain the correct values.
To me, and others I've mentioned this to, it makes no sense.
There are times that I can have identical cell contents for different cells,
and it will not recognize them. As such it will give me "individual" totals,
instead of summing the contents as is sought. If I take one of the cell's
values, and "coerce it" the different values will all be recognised under
each of the cells that the sumproduct formula is in, and I then must remove
the duplicates to get my total.
All of this brings me back to the issue of why doesn't sumproduct just look
at the cell contents, instead of the formatting, or other properties of that
cell?
What does it take to get it to always look at JUST THE CELL CONTENTS, and
none of the other properties-- unless I specify those specific properties?
This formula will work on the source page, but when I go to move it to a
secondary, or tertiary page it will not recognize the cells being tested
against due to these issues. grrrrr!
Somewhere in the background I can hear Elmer Fudd saying, "scwewy wabbit!!!"


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