Sumif Linking to Another Workbook error #VALUE!

[drgka55]

Please check these posts for the same problem:

http://groups.google.com/group/micr...2d5e07f090b/a0fe7a93fe087156#a0fe7a93fe087156


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?

 

[tim m]

Perhaps column E was formatted to text or was a formula and he put the actual
number value in the cell?

 

[Bob Phillips]

He means that a cell that he was trying to sum was formatted as text, so it
didn't add in. However, the main thrust of the item was that SUMIF doesn't
work on a closed workbook, so the suggestion was to use SUMPRODUCT

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[JMB]

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.