I think the main issue is the data type in one workbook was not the same as
the type you were trying to match to (ie numeric vs. text).

Where A1:A10 is formatted as text and contains 1's and 2's (again-formatted
as text), B1:B10 contains numbers, and B15 contains the number 1 (formatted
as a number):

=SUMIF(A1:A10,B15,B1:B10) returned 25
while =SUMPRODUCT(--(A1:A10=B15),B1:B10) returned 0

Sometimes Excel will try to save the user from his/herself by converting
text numbers to numeric format (if it can). In this case, it looks like
Sumif will treat text numbers the same as numeric numbers while Sumproduct
will not.

It is a good habit to ensure the data you are comparing is the same type and
not rely on excel to try to interpret what you are attempting to do.

"drgka55" wrote:

Please check these posts for the same problem:

http://groups.google.com/group/micro...fe7a93fe087156


The last post is :

"The original Sumif is looking at the same cells and brings back a
value when
the other workbook is open so that can't be it.

Woohoo, it appears that column e wasn't a value, I changed it to be a
value
and it works. I can't believe it was such a simple fix. I didn't
think the
normal =sumif() would work if it wasn't a value.


You're a genius.


Thanks heaps. "



I do not get the solution to the problem. What exactly does he mean
when he changed column E to be a value?