determining if a cell is blank ... if/or statement needed

[Judo Tom]

Hello,

I have an eq that uses indirect lookups and adds together 3 fields lets say a1, b1, c1 all the a,b,c fields are on the same page but not the same page where i need to do the equation.

now using the indirect lookups and adding the values together works if all fields contain values. The problem is when they do not. I need to check to see which ones are blank and then add together only the fields that contain information.

What would be the best way to do this??? At first i was thinking of a nested if/or statement but im thinking that there has to be a better way and i am just not thinking of it right now...

Any help would be appreciated...

Thanks,
Tom

 

[Judo Tom]

how bout this...

if i type in:

=sum(INDIRECT($B$1&"AI"&A2):INDIRECT($B$1&"AG"&A2):INDIRECT($B$1&"DO"&A2))
it returns 22266393

if i type in:
=sum(INDIRECT($B$1&"AI"&A2),INDIRECT($B$1&"AG"&A2),INDIRECT($B$1&"DO"&A2))
it returns 174

if i type in:
=sum(INDIRECT($B$1&"AI"&A2):INDIRECT($B$1&"AG"&A2),INDIRECT($B$1&"DO"&A2))
it returns 49368

none of those are correct

it should be 4582 because that is what is in one of the cells and the other 2 are blank for this case...

i appreciate all the help ive been getting ill post later 2nite with what i have tried and what happened...

 

[bomalley]

You could use =IF(ISBLANK(cell),...) Or if the cells contain a formula, use =IF(cell="",...)

_________________
Cheers,
Brandon
This message was edited by bomalley on 2002-10-09 17:18

 

[Aladin Akyurek]

On 2002-10-09 16:54, Judo Tom wrote:
how bout this...

if i type in:

=sum(INDIRECT($B$1&"AI"&A2):INDIRECT($B$1&"AG"&A2):INDIRECT($B$1&"DO"&A2))
it returns 22266393

if i type in:
=sum(INDIRECT($B$1&"AI"&A2),INDIRECT($B$1&"AG"&A2),INDIRECT($B$1&"DO"&A2))
it returns 174

if i type in:
=sum(INDIRECT($B$1&"AI"&A2):INDIRECT($B$1&"AG"&A2),INDIRECT($B$1&"DO"&A2))
it returns 49368

none of those are correct

it should be 4582 because that is what is in one of the cells and the other 2 are blank for this case...

i appreciate all the help ive been getting ill post later 2nite with what i have tried and what happened...

In AI3 in INPUTCY I have the value 4582. Nothing in AG3 and in DO3...

Here is the result using simple

=SUM(INDIRECT(B1&"AI"&A2),INDIRECT($B$1&"AG"&A2),INDIRECT($B$1&"DO"&A2))

Book4
	A	B	C	D
1		INPUTCY!
2	3	4582
3
Sheet2

 

[Yogi Anand]

Hi Judo Tom

It appears that the simple SUM function should work in your case whether the value in any of the three cells of interest is 0, a blank or is greater than 0

y021009h1.xls
	A	B	C	D	E
1		InputCY!
2	3
3
4
5	AI3	0		4582	formula=SUM(INDIRECT($B$1&"AI"&A2),INDIRECT($B$1&"AG"&A2),INDIRECT($B$1&"DO"&A2))
6	AG3	4582		0	formula=SUM(AJ5,AH5,DP5)
7	DO3	0		4582	formula=SUM(B5,B6,B7)
Sheet4b

</SPAN>

So, please post back and let us take it from there.

Regards!

Yogi

Edit: InputCY! in cell B1, and 3 in cell A2, and reposted the new worksheet simulation
This message was edited by Yogi Anand on 2002-10-09 19:31

 

[Judo Tom]

Im going to start playing with this again...

just to get back to you guys though in cell b1 is the value 'InputCY'!

this is the name of the worksheet that contains the three cells i need to sum up

 

[Yogi Anand]

Hi Judo Tom:

I have edited my last post wherein I have housed InputCY! in cell B1 and 3 in cell A2, and it shows that the simple SUM function should work in your case.

Regards!

Yogi

 

[Judo Tom]

Just wanted to thank you guys for the time and effort you guys spent helpin me out..

Hope you dont mind but I might have to ask a few more questions in the near future....