SUMIF question

[aMember]

I know I can't use references to other workbooks as parameters to SUMIF. It returns #VALUE errors if the workbook isn't open.

However, I read on google someone using
SUM(IF([workbook]Sheet1!A1:A10<=aValueInThisWB, [workbook]Sheet1!D110,0))

I could not get this to work. Anyone see any problems with it?

 

[srogers]

I have the formula below in Book1 and a value in Book1 in cell B4. Book 2 is closed and has values in A1 thru A10.
The formula displays the correct result.
Is that what you were looking to do?


=SUM(IF('YourPath[Book2.xls]Sheet1'!A1:A10<=Sheet1!B4,Sheet1!D110,0))

 

[aMember]

Can I ask which Excel you are running? I'm currently using Excel 2000 but this application will be 'downgraded' to Excel 97 (so I'm careful about what I use).
It doesn't work for me. I even put the equation on my sheet and used values only on this sheet and it didn't work. You actually tried it?

Thanks so much for your time.
Andrea

 

[srogers]

Sure - I'm using Excel 2000. I would try it on Excel 97 but I'm not sure if I have a copy. I'll look around.

I tried it - and I still have the workbooks. If you like I could email them to you. My email is: phototkr@hotmail.com

Once you save the files, you may have to go into Book1 (the one with the formula) and on the menu go to Edit, Links, (and on the window that opens) Change Source, and navigate to the location that Book2 is in.

Sharon

 

[srogers]

Oops - I just realized it seems to be only looking at the figure in A1 instead of the total of A1:A10.

I added Sum to add them up - and some parenthesis.

=SUM(IF(SUM([Book2.xls]Sheet1!A1:A10)<=Sheet1!B4,Sheet1!D110,0))

Sorry about that.
Sharon

 

[aMember]

No need to try it on Excel '97. I can do that. I just couldn't even get it to work on Excel 2000. But, it is exactly what I wanted so if you say it works, I obviously and doing something wrong. Will give it a shot tonight.
Thanks again,
Andrea

 

[srogers]

Ok, if you decide you want the workbooks just email me your email address and I'll send them on.
Sharon