 Status
Not open for further replies.
1 - 10 of 10 Posts

#### scsisys

·
##### Registered
Joined
·
92 Posts
Discussion Starter · ·
Using an Excel 97 based spreadsheet (Spread32) and I'm stuck trying to get the sum of -3- different cells in -3- different workbooks.

What I want is a formula to return a BLANK entry in A1 of Workbook1, IF A1 in Workbook2 is BLANK OR IF A1 in Workbook3 is BLANK OR IF A1 in Workbook4 is BLANK ; otherwise, the SUM of all 3 cells in A1 of Workbook1 (assumes a value in each of the -3- cells).

I can get a formula to give me a #value error message based on an example from a post here on 30 Jun 09 by EVH. Just can't figure out what I'm missing.

Thanks
ray

#### turbodante

·
##### Registered
Joined
·
744 Posts
Hi Ray, I think what you really need is the AND, rather than use OR if i understand you correctly.

In Cell A1 or book1:
Code:
``= if (AND([Book2]Sheet1!A1="",[Book3]Sheet1!A1="",[Book4]Sheet1!A1=""),"BLANK",sum([Book2]Sheet1!A1+[Book3]Sheet1!A1+[Book4]Sheet1!A1))``
HTH

#### scsisys

·
##### Registered
Joined
·
92 Posts
Discussion Starter · ·
Turbodante...

Well, I plugged your formula in and still get the #value error message (after adjustments for the path/name ).

I have not worked at all with AND/OR statements but the way I read your formula is if all
3 cells are blank, then a blank entry would be in Book1,A1. Otherwise, the sum of all 3 cells.

What I need is if any 1 of the 3 cells is blank, then Book1,A1 would be blank. Otherwise, the sum of all 3 cells in Book1,A1.

Thanks,
ray

#### turbodante

·
##### Registered
Joined
·
744 Posts
I see.. in that case OR is the feller you need.

=IF(OR([Book2]Sheet1!A1="",[Book3]Sheet1!A1="",[Book4]Sheet1!A1=""),"BLANK",SUM([Book2]Sheet1!A1+[Book3]Sheet1!A1+[Book4]Sheet1!A1))

#### scsisys

·
##### Registered
Joined
·
92 Posts
Discussion Starter · ·
Turbodante...

No change...#value error message.

Checked the cell contents of the 3 workbooks and 2 of them have numeric values and the third is blank (except for a formula which sums several cells). Does the formula count as text, which could result in the error message ??

Thanks
Ray

#### turbodante

·
##### Registered
Joined
·
744 Posts
So the plot thinkens... I think we just need to tweek the formula to look for, what I thought was [blank] "".

Checked the cell contents of the 3 workbooks and 2 of them have numeric values and the third is blank (except for a formula which sums several cells). Does the formula count as text, which could result in the error message ??
What result is returned from this formula, and what is the formula?

#### turbodante

·
##### Registered
Joined
·
744 Posts
Actually, I think this variation may work...

=IF(AND(ISNUMBER([Book2]Sheet1!A1),ISNUMBER([Book3]Sheet1!A1),ISNUMBER([Book4]Sheet1!A1)),SUM([Book2]Sheet1!A1+[Book3]Sheet1!A1+[Book4]Sheet1!A1),"BLANK")

#### scsisys

·
##### Registered
Joined
·
92 Posts
Discussion Starter · ·
Turbodante....

This is the formula in the Workbook/cell that contains no numeric value:

=IF(SUM(AC5,AE5,AG5)=0,"",SUM(AC5,AE5,AG5))

My "blank" term is what I refer to as a cell with no numeric value in it. In the above formula , "" = blank(empty cell).

No change; #value error message.

Have got to take off for about 2 hrs.

Thanks
ray

#### turbodante

·
##### Registered
Joined
·
744 Posts
Did you try formula in #7? = I can not recreate the error; I'm stumped at this point If formula in #7 is , can you post up a example workbooks so we can see the problem.

#### scsisys

·
##### Registered
Joined
·
92 Posts
Discussion Starter · ·
Turbodante...

Yes, I tried the formula in # 7 and still got the #value error message. Tried various
edited variations of it with no success.

So, I decided to just do a nested IF statement and that worked. I'm still going to play
around with the other IF formulas you provided and see what it takes to get them to
function.

Thanks for all your suggestions!!

ray

1 - 10 of 10 Posts
Status
Not open for further replies.