I've built a model that uses the SUMIFS function rather frequently, and we have some people opening the model in Excel 2007 that receive an error (#NAME I believe).
What is causing this problem, and what can be done to fix it? If they're using 2003, I tell them that's the problem, but I'm not sure what to tell someone in Excel 2007 or guide them how to fix it.
Thanks!
Last edited by sofib09; 01-21-2011 at 12:16 PM.
If they are using XL 2003, then yes that is the problem and that is the error...
You will need to convert the SUMIFS formula to possibly a SUMPRODUCT construct in order to be backward compatible.
If you post the current formula, we can convert it for you.
If it is happening in 2007, check that text strings in the formula. Again post sample or formula if possible that causes the error in 2007.
Last edited by NBVC; 01-13-2011 at 04:10 PM.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
If they are using XL2007 as you say then the use of SUMIFS per se is not the issue... can you shed some light on the formula generating the error ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
We're working with multiple databases, so my formula usually goes along the lines of
SUMIFS(INDEX(XXX:XXXX,MATCH(XX,XXX:XXX,0)),XXXX:XXXX,Criteria,XXXX:XXXX,Criteria))
for however many criteria I have. I'm able to open and view it just fine on my computer, and others at my job are able to view it just fine on their computers, so I'm not sure why someone on a PC with Excel 2007 would have an issue.
Thanks!
Not sure why that would cause #NAME error on another 2007 version.. Are you sure about the error code? Are you sure they are in 2007? Are you sure they don't muck around with entries or formula?
I'm not 100% certain what they might be doing on their end.
All I know is that they get an error in Excel 2007 when they open the file. I did save it with an Excel 2003 extension and told them to open it in Excel 2007, where it should automatically go into compatibility mode. Not sure if that would work though.
Any other ideas?
That would be it, if they are opening in 2003....
We you saved as .xls, you should have got warnings about significant loss of functionality... but not sure it causes issue in re-opening in 2007.
I don't think so...the original file I sent to them (and they claim they opened in 2007) was a .xlsx (2007) file.
Well, if anyone has any ideas as to why an Excel 2007 file with the SUMIFS formula wouldn't work on another computer using Excel 2007, please feel free to let me know how to recommend a fix. Thanks!
I sent the .xls file only after they reported the initial error.
Thanks for the input, though! Now I know that's not a good solution.
I suspect neither NBVC nor I made ourselves very clear.Originally Posted by sofib09
If you distributed an .xlsx file to another user and the SUMIFS displays #NAME? it has nothing to do with SUMIFS per se... you would need to review your formula in more depth and analyse the precedents
(if for ex. you had a precedent which relied on a UDF then that would generate #NAME? given .xlsx does not support VBA - ie there is no longer a UDF)
Oh, okay.
Well, there's not VBA or anything like that in the file, so I'm not sure what could be the issue.
I'll poke around and see what I can find, though.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks