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

 

[Andrew Poulsom]

Try incorporating COUNTA in your formula. It counts the number of cells that are not empty. Example:

=IF(COUNTA(A1:A3)<3,"",SUM(A1:A3))

 

[Aladin Akyurek]

What is the formula that you use to add up for

=SUM(A1:C1)

would simply suffice?

 

[Judo Tom]

here is the formula i use now.. it works if all cells are filled...

=(INDIRECT($B$1&"AI"&A2)+ INDIRECT($B$1&"AG"&A2)+ INDIRECT($B$1&"DO"&A2)

B1 = 'INPUTCY!' which is the name of the worksheet where the cells are located
a2 = 3 which is the row where the cells are located...

sorry I was vague in my first post...


Thanks again for the help...

 

[Yogi Anand]

Hi Judo Tom:

I have used three case in the following worksheet simulation --

case 1 ... where one of the entries is blank
I could use simple sum formula -- I was not able to reproduce, the situation you have with indirect lookups and so forth

Case 2 ... here I used #N/A in one of the cells; so simple sum formula did not work -- so Iused an array based formula with the check that each entry was numeric

Case 3 ... here I used numeric entries in all cases -- and both the simple sum formula and the array based sum formula both worked

So, my array based sum formula:

{=SUM(IF(ISNUMBER(A1:C1),A1:C1))}
worked in all cases;

and so does the SUMIF formula

=SUMIF(a1:C1,">0")<SPAN id='ForSubmit'>

y021009h1.xls
	A	B	C	D	E	F	G
1	1		3	4	4	4	Case1
2		noteB1isblank		simplesumformula	sumifformula	arraybasedsumformula
3
4
5	1	#N/A	3	#N/A	4	4	Case2
6				simplesumformula	sumifformula	arraybasedsumformula
7
8
9	1	2	3	6	6	6	Case3
10				simplesumformula	sumifformula	arraybasedsumformula
Sheet4

</SPAN>

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

Yogi

Edit: added the SUMIF formula in the simulation
This message was edited by Yogi Anand on 2002-10-09 15:52

 

[Aladin Akyurek]

On 2002-10-09 15:30, Judo Tom wrote:
here is the formula i use now.. it works if all cells are filled...

=(INDIRECT($B$1&"AI"&A2)+ INDIRECT($B$1&"AG"&A2)+ INDIRECT($B$1&"DO"&A2)

B1 = 'INPUTCY!' which is the name of the worksheet where the cells are located
a2 = 3 which is the row where the cells are located...

sorry I was vague in my first post...


Thanks again for the help...

Try:

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

Aladin

 

[Judo Tom]

On 2002-10-09 15:34, Aladin Akyurek wrote:

On 2002-10-09 15:30, Judo Tom wrote:
here is the formula i use now.. it works if all cells are filled...

=(INDIRECT($B$1&"AI"&A2)+ INDIRECT($B$1&"AG"&A2)+ INDIRECT($B$1&"DO"&A2)

B1 = 'INPUTCY!' which is the name of the worksheet where the cells are located
a2 = 3 which is the row where the cells are located...

sorry I was vague in my first post...


Thanks again for the help...

Try:

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

Aladin

aladin: that did not work.. thanks for the help though...

i will try the other example in a bit and post back... THANKS FOR THE HELP GUYS!!!

 

[Aladin Akyurek]

On 2002-10-09 15:49, Judo Tom wrote:

On 2002-10-09 15:34, Aladin Akyurek wrote:

On 2002-10-09 15:30, Judo Tom wrote:
here is the formula i use now.. it works if all cells are filled...

=(INDIRECT($B$1&"AI"&A2)+ INDIRECT($B$1&"AG"&A2)+ INDIRECT($B$1&"DO"&A2)

B1 = 'INPUTCY!' which is the name of the worksheet where the cells are located
a2 = 3 which is the row where the cells are located...

sorry I was vague in my first post...


Thanks again for the help...

Try:

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

Aladin

aladin: that did not work.. thanks for the help though...

i will try the other example in a bit and post back... THANKS FOR THE HELP GUYS!!!

What did you get as result? Do the INDIRECT bits return results as they are supposed to?
This message was edited by Aladin Akyurek on 2002-10-09 15:57

 

[Judo Tom]

Yogi: First thanks a ton...

the first case best describes my situation my only problem is that my data elements are not arranged a,b,c the columsn are AI, AG, DO

so i tried to replace the sum function you gave me and instead and used the format (ai:ag:do) but it seems my values are VERY HIGH

this is the formula i tried:

=SUM(if(ISNUMBER(INDIRECT($B$1&"AI"&A2): INDIRECT($B$1&"AG"&A2):INDIRECT($B$1&"DO"&A2)), INDIRECT($B$1&"AI"&A2):INDIRECT($B$1&"AG"&A2):INDIRECT($B$1&"DO"&A2)))

thanks again!

 

[Aladin Akyurek]

On 2002-10-09 16:31, Judo Tom wrote:
Yogi: First thanks a ton...

the first case best describes my situation my only problem is that my data elements are not arranged a,b,c the columsn are AI, AG, DO

so i tried to replace the sum function you gave me and instead and used the format (ai:ag:do) but it seems my values are VERY HIGH

this is the formula i tried:

=SUM(if(ISNUMBER(INDIRECT($B$1&"AI"&A2): INDIRECT($B$1&"AG"&A2):INDIRECT($B$1&"DO"&A2)), INDIRECT($B$1&"AI"&A2):INDIRECT($B$1&"AG"&A2):INDIRECT($B$1&"DO"&A2)))

thanks again!

Somehow I'm not convinced...