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

Judo Tom

Board Regular
Joined
Sep 5, 2002
Messages
53
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
Joined
Jul 21, 2002
Messages
73,092
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))
 

Judo Tom

Board Regular
Joined
Sep 5, 2002
Messages
53
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
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Sep 5, 2002
Messages
53

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Sep 5, 2002
Messages
53
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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...
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top