PDA

View Full Version : SUM(IF across multiple sheets in another workbook



belly0fdesir
05-16-2007, 04:50 PM
This is probably a rather simple question for this forum, but I've tried getting it answered on other forums, but I've gotten further, I think, on my own. I started with:



=IF(F4="","",SUM(
IF('[LOG SHEET 2007.XLS]January'!$B$2:'[LOG SHEET 2007.XLS]December'!$B$341>0,
IF('[LOG SHEET 2007.XLS]January'!$C$2:'[LOG SHEET 2007.XLS]December'!$C$341="",
'[LOG SHEET 2007.XLS]January'!$W$2:'[LOG SHEET 2007.XLS]December'!$X$341))))


Which just looked all wrong to me to begin with. I figured out that if I were just working with sheets WITHIN my current workbook, I could get it to work using the method shown below, but as soon as I try to get the formula to work with the data in the other workbook, I get a #REF! error. Can someone tell me what is wrong with the formula shown below and what is the proper way to accomplish my goal?



=SUM(
IF('[LOG SHEET 2007.XLS]January:December'!$B$2:$B$600>0,
IF('[LOG SHEET 2007.XLS]January:December'!$C$2:$C$600="",
'[LOG SHEET 2007.XLS]January:December'!$W$2:$X$600)))



i think i'm closer, but just can't get it to work for me :/

Bob Phillips
05-17-2007, 02:32 AM
Did you array enter it?

belly0fdesir
05-17-2007, 09:21 AM
Yes... I work with Array Formulas all the time, but have never tried doing this across multiple sheets on another workbook...

What if I just wanted to SUM across multiple worksheets on another workbook, how would I do that? Maybe that will help me to figure out how to do this...

You see... If I wanted to SUM across multiple worksheets in my CURRENT workbook, I would do:

=SUM(SHEET1:SHEET3!$B$2:$B$300)

I can do the same thing with SUM(IF but when I involve another workbook in the formula, with the apostrophes, I'm not sure how the forumula is supposed to be written...


EDIT: actually... SUM(IF will NOT work when I try it using this same method... that maybe my problem. Maybe you just can't have Array formulas that reference a range of sheets.

EDIT EDIT: Someone on another forum linked me to this plugin (http://xcell05.free.fr/) which is supposed to achieve the goal I'm reachin' for. Now I just gotta get my IT staff to approve the pluging :/