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

#### Judo Tom

##### Board Regular
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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Andrew Poulsom

##### MrExcel MVP
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))

##### MrExcel MVP
What is the formula that you use to add up for

=SUM(A1:C1)

would simply suffice?

#### Judo Tom

##### Board Regular
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

##### MrExcel MVP

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
ABCDEFG
113444Case1
2noteB1isblanksimplesumformulasumifformulaarraybasedsumformula
3
4
51#N/A3#N/A44Case2
6simplesumformulasumifformulaarraybasedsumformula
7
8
9123666Case3
10simplesumformulasumifformulaarraybasedsumformula
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

##### MrExcel MVP
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))

#### Judo Tom

##### Board Regular

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: 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!!!

##### MrExcel MVP
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: 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

##### Board Regular
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!

##### MrExcel MVP
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...

Replies
11
Views
296
Replies
7
Views
130
Replies
1
Views
108
Replies
9
Views
293
Replies
4
Views
111

### Forum statistics

1,144,218
Messages
5,723,069
Members
422,477
Latest member
pete101 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back