SUMIF or Sum(IF(( neither working

[dcavaiani]

I have Excel 2002. I have a spreadsheet which accesses data conditionally from a 2nd spreadsheet (not the same spreadsheet) and SUMS it.

I have tried SUMIF which seems to have issues in the 2002 version, and I have also tried -SUM(IF((...)) which is not working either. Pretty stumped at this point!

=SUM(IF(('C:\Bobs Business\[Invoicing.xls]Invoicing'!C2:C199="D E P O S I T"),'C:\Bobs Business\[Invoicing.xls]Invoicing'!G2:G199,0))

Returning back zeroes. The "C" column is alpha, and the "G" column in numeric.

I'm lost right now.

 

[NauticalGent]

I beleive you are trying to use the immediate If function(IIF) instead of the If Function.

Try replacing your IF statements with IIF and see how that works for you.

 

[June7]

AFAIK, IIf() is not available in Excel. But maybe 2002 was different. SumIf should be available in 2002.

Use SumIf when you want to use a range as criteria. If() cannot handle that.

=SUMIF('C:\Bobs Business\[Invoicing.xls]Invoicing'!C2:C199, "D E P O S I T", 'C:\Bobs Business\[Invoicing.xls]Invoicing'!G2:G199)

 

[dcavaiani]

Thanks to the 2 who responded. Do to the timing, I have already done a work around that included another field in the source table and just the SUM function which I know works well - w/o the source Excel having to be opened first.

 

[Scribtor]

IIF isn't a thing in Excel, as untill recently, I had worked with the same version of Excel as the poster


My suggestion, if you want to expand upon this and learn for future reference, use the formula builder popup, because it will show you errors immediately before finishing up the formula and has this neat thing called tooltips (maybe you have to enable them from options, if they're not default enabled) that help you follow the formula syntax required


Tab into the next segment of syntax immediately lets excel evaluate what's gonna happen so you can "debug" even before you blow it up

Cheers!