SUMIF or Sum(IF(( neither working (1 Viewer)

dcavaiani

Registered User.
Local time
Today, 04:53
Joined
May 26, 2014
Messages
385
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

Ignore List Poster Boy
Local time
Today, 05:53
Joined
Apr 27, 2015
Messages
6,280
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

AWF VIP
Local time
Today, 01:53
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 04:53
Joined
May 26, 2014
Messages
385
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

Registered User.
Local time
Today, 10:53
Joined
Sep 27, 2018
Messages
16
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!
 

Users who are viewing this thread

Top Bottom