Science & Industry

Since I honestly don't know squat about creating excel spreadsheets and really can't afford at this time to upgrade MS Office 2010, was wondering if any of you excel gurus out there could assist me.

I've been looking for a good capital production spreadsheet and came across this one:

https://forums.eveonline.com/default.aspx?g=posts&t=68837

Looks like an awesome spreadsheet and does precisely everything I'm looking for in a nice, clean format.

HOWEVER, in converting it to 2003, a giant section of it gets jacked due to, I'm assuming, an unrecognized formula.

=_xlfn.SUMIFS('Capital Components'!$D:$D,'Capital Components'!$B:$B,H$5,'Capital Components'!$A:$A,$A8)*$D8

This is the formula shown and creates an output showing: #name?

I searched online but all I come up with is to change the "_xlfn.SUMIFS" part to "sumproduct" However, all that seems to accomplish is to give me the output: #number!

Any help on converting this properly? Or am I missing something entirely here?

Thanks for the suggestions.

Unfortunately, I attempted to use Open Office and it didn't recognize the "sumifs" formula either. I'm currently downloading and installing LibreOffice to see if that will work. (Shoot....even google docs didn't know what to do with it.)

I also attempted to open it in 2010 at a college computer and tried to save it in a backwards compatible version and it just gave me errors/warnings that this particular function would do exactly what it's doing in 2003 but did not offer any 'Excel for Dummies' type of assistance in making it compatible.

So I guess I'll cross my fingers and hope LibreOffice works once I get it installed. Otherwise any help in comprehending the formula and what else needs to be changed in the cell other than the "sumifs" to "sumproduct" to make these lines work would be awesome.

EDIT: Just finished installing LibreOffice and opened the file. No luck there either. Apparently sumifs is an Excel 2010 only function.

You can make that formula work in Excel 2003 if you rewrite it as a SUMIF function and you specify the range rather than have Excel find it for you autmatically. The SUMIFS is much, much more elegant though as you don't have to modify it for every single component..

However, this formula only seems to appear in the Capital Ships page columns H:N and, as far as I can see it doesn't get used anywhere. You can safely delete everything in column H:N to remove the warnings and the sheet still works fine (in my limited testing).

I'll give that a go. Thanks for the input!



I admit I tried something like that before posting this thread but everyone I tried were crap.