Can anyone break this Excel formula down for me?

J

Jonny Two Shoes

Expert Member
Joined
Nov 13, 2006
Messages
4,542
Reaction score
17
Location
Gauteng
Hi there,

The following Excel formula...

=SUMIF('L:\09 Monthly\[** SLSACT2009.01.xls]DATA'!$GG:$GG,H91,'L:\09 Monthly\[** SLSACT2009.01.xls]DATA'!$AF:$AF)/1000

Could anyone break it down piece by piece for me so that I can understand what it is supposed to be doing? Would be much appreciated. I looked at a typical sumif formula and can't match it to this one. This formula returns a #VALUE! error in Excel2003 but works perfectly fine in Excel2000. Ignore the stars...I just sensored that small bit but is part of the file name.
 
SUMIF('L:\09 Monthly\[** SLSACT2009.01.xls]DATA'!$GG:$GG,H91,'L:\09 Monthly\[** SLSACT2009.01.xls]DATA'!$AF:$AF)/1000

L:\09 Monthly\[** SLSACT2009.01.xls] refers to a file.
In that file, if the inputs in the Data tab in column GG is equal H91, then sum (from the same file), the data in column AF.

Divide that by 1000.

Is the a file called ** SLSACT2009.01.xls in L:\09 Monthly?
 
Yes except the stars represent the letters of the company I work for which I have censored :p

Thanks Milomak.

I have to try and figure out why it is giving the #VALUE! in Excel 2003 but not 2000 and am totally stumped :( whether I choose to update the workbook or not there are #value! everywhere and they don't change. Although I have found if I open some of the source workbooks then the figures start to appear but there are many sources and references and would be a nightmare to go through each one all the time :/

I even phoned Microsoft about this and they said sorry can't help :( I thought maybe if I understood the formula better I could play around some more.
 
I created my own SUMIF using the fx button...I am learning as I go :)

The file referenced is on a network drive and the file with the SUMIF is on my desktop. If the referenced file is open the value is fine. If not then if I select "update" on open it shows #value!.

EDIT: I just tried with both files locally as well and the same thing happens. It seems SUMIF doesn't work if the referenced file is not opened...period!! :confused: at least that takes the network out of it
 
Last edited:
It could be that the sumif function either doesn't exist in office 2000, or is more limited/defined differently in office 2000 than in 2003. I had a similar problem with 2k7 and 2k3, with certain functions not existing in 2k3
 
greggpb said:
#VALUE means one of the parameters to the function are not valid...
Click to expand...

I think it's lying though because it works fine when the source is open, therefore nothing wrong with the formula :/ must be a bug with 2003? I am going to play around some more...maybe there is an option somewhere I should check or something.
 
You do need the source file open.
Whenever u see the a file's path in the formula, you'll know.
When the source is open, you'll notice that the formula changes.

When you use vlookups, u don't need to have the source open for some reason.
 
^ I see....but why in 2000 then did I not need the source open? Wonder why Microsoft changed that :/

I just feel sorry for the user because we need to install 2003 on his PC (no choice...orders from the Americans :rolleyes:) and all I can do is tell him sorry for you, you need to go and change 2 million cells and references everywhere because 2003 doesn't support SUMIF the same anymore :( I'm sure he is going to be very happy with me after that :/
 
Jonny Two Shoes said:
^ I see....but why in 2000 then did I not need the source open? Wonder why Microsoft changed that :/

I just feel sorry for the user because we need to install 2003 on his PC (no choice...orders from the Americans :rolleyes:) and all I can do is tell him sorry for you, you need to go and change 2 million cells and references everywhere because 2003 doesn't support SUMIF the same anymore :( I'm sure he is going to be very happy with me after that :/
Click to expand...

U can make an auto_open macro that opens the sheets when he opens the workbooks, and then closes them again.
Not hard to do.

Or just make a macro button that he can push which does it, and calculates the sheet.
 
Hmmmm...it's actually very easy :) and it works. Although a bit of a messy workaround at least it's something. Thanks again alf101 :)
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X