Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-04-2018, 11:48 AM   #1
dcavaiani
Newly Registered User
 
Join Date: May 2014
Location: Appleton, WI
Posts: 222
Thanks: 109
Thanked 10 Times in 10 Posts
dcavaiani is on a distinguished road
SUMIF or Sum(IF(( neither working

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.

dcavaiani is offline   Reply With Quote
Old 10-07-2018, 09:35 PM   #2
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,559
Thanks: 330
Thanked 198 Times in 174 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: SUMIF or Sum(IF(( neither working

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.
__________________
“I had the RIGHT to remain silent...but I didn’t have the ABILITY.” - Ron White
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
dcavaiani (10-08-2018)
Old 10-08-2018, 02:15 AM   #3
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 738
Thanks: 0
Thanked 169 Times in 169 Posts
June7 will become famous soon enough
Re: SUMIF or Sum(IF(( neither working

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)

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
dcavaiani (10-08-2018)
Old 10-08-2018, 04:30 AM   #4
dcavaiani
Newly Registered User
 
Join Date: May 2014
Location: Appleton, WI
Posts: 222
Thanks: 109
Thanked 10 Times in 10 Posts
dcavaiani is on a distinguished road
Re: SUMIF or Sum(IF(( neither working

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.
dcavaiani is offline   Reply With Quote
Old 10-08-2018, 10:53 PM   #5
Scribtor
Newly Registered User
 
Join Date: Sep 2018
Posts: 16
Thanks: 8
Thanked 0 Times in 0 Posts
Scribtor is on a distinguished road
Talking Re: SUMIF or Sum(IF(( neither working

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!

Scribtor is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with SUMIF please Big Pat Excel 1 04-16-2010 02:10 AM
Sumif LadyDi Excel 3 01-16-2009 12:30 PM
Sumif ????? MGumbrell Excel 3 09-17-2007 08:29 AM
sumif Jeff06 General 3 01-15-2007 01:12 PM




All times are GMT -8. The time now is 03:10 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World