# 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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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))

What is the formula that you use to add up for

=SUM(A1:C1)

would simply suffice?

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...

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

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))

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

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

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!

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
1
Views
248
Replies
0
Views
631
Replies
4
Views
151
Replies
5
Views
242
Replies
4
Views
376

1,218,890
Messages
6,145,020
Members
450,586
Latest member
hehehihi2007

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