If cell blank OR another cell blank then show blank, if not display value

**stevop622** · 04-09-2013, 02:22 AM

Hi I am working on an excel sheet and have run into a problem.

I wish to display the value of cell D13 if D13 OR A13 are not blank. So if neither of these cells are blank then the cell should display the value of D13, if not, it should show blank.

I wish to count the "non blank" cells after that.

Please help.

**AndyLitch** · 04-09-2013, 02:32 AM

You contradicted yourself slightly there so it's a little unclear whether you want Or/AND

This will show the value of d13 if either a13 or d13 have values
=IF(OR(A113<>"",D113<>""),D13,"")

This will only show a value if both A13 and D13 have values
=IF(AND(A113<>"",D113<>""),D13,"")

**benishiryo** · 04-09-2013, 02:40 AM

hi stevop622, welcome to the forum. like Andy mentioned, i think you meant if D13 AND A13 are not blank? if you don't mean that, what happens when only A13 is filled? or if only D13 is filled? if you meant it as "and", then:
=IF(AND(A13<>"",D13<>""),D13,"")

are you copying the formula downwards & want to count how many non-blank? not sure what you counting. and is D13 a text, number or either? for text:
=COUNTIF(E13:E20,">""")
for number:
=COUNT(E13:E20)
for both:
=ROWS(E13:E20)-COUNTBLANK(E13:E20)

**stevop622** · 04-09-2013, 02:45 AM

Thanks, I used the second option which works. I have the set up the cell next door to display a 0 if the cell displays a value (in this case D13) and then count all the zeros to give me the total number of lines in which both cells have values.

Is there a way of instead of just showing the cell next door to be blank, but also to bring all the zeros to the top of the column? for example out of 100 lines, 8 meet the criteria of having both the A and D cells having values, but i want those 8 cells to all appear together and not have several blanks between them.

**stevop622** · 04-09-2013, 02:47 AM

The value displayed will be text and not a number

**muniraja.g** · 04-09-2013, 02:49 AM

If you use =round(a1,4) in cell A1 and similarly in Cell A2, iam sure u will not get the value in Cell A3 as 0.179999999 etc.,,
Pls try using formula =Round

**AndyLitch** · 04-09-2013, 04:07 AM

Originally Posted by stevop622

Thanks, I used the second option which works. I have the set up the cell next door to display a 0 if the cell displays a value (in this case D13) and then count all the zeros to give me the total number of lines in which both cells have values.

Is there a way of instead of just showing the cell next door to be blank, but also to bring all the zeros to the top of the column? for example out of 100 lines, 8 meet the criteria of having both the A and D cells having values, but i want those 8 cells to all appear together and not have several blanks between them.

You can sort the data based on the column with the zero's..