# Formula display if no data

#### asyamonique

##### Well-known Member
Code:
``=COUNTIF(\$D\$3:\$D\$500,O2)``

Good Day,
Above formula works fine but why there are numerics ıf there is no any data from column D and E ?
Also how can I avoid to display in cells #N/A if no values on related column with given formula below?
Code:
``=VLOOKUP(O2,\$D:\$F,2,0)``
Many Thanks

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Not sure I understand first question. Are you wanting =IF(COUNTIF(\$D\$3:\$D\$500,O2)=0,"",COUNTIF(\$D\$3:\$D\$500,O2))

Second one try =IFERROR(VLOOKUP(O2,\$D:\$F,2,0),"")

Hope that helps.

Are you wanting =IF(COUNTIF(\$D\$3:\$D\$500,O2)=0,"",COUNTIF(\$D\$3:\$D\$500,O2))
Can be shortened to:

=IFERROR(1/(1/COUNTIF(\$D\$3:\$D\$500,O2)),"")

Thanks for both help but the numeric value still appear due to O2 cell has another formula I think that is the reason!
O2 fourmula is
Code:
``=IF(O1="","",IFERROR(SMALL(\$D\$3:\$D\$400,COUNTIFS(\$D\$3:\$D\$400,"<="&O1)+1),""))``
Thanks

Code:
``=SUMIF(\$D:\$D,O2,\$J:\$J)/60``
Code:
``=IF(COUNTIF(\$D\$3:\$D\$500,O2)=0,"No Values",SUMIF(\$D\$3:\$D\$500,O2,\$K\$3:\$K\$500)/COUNTIF(\$D\$3:\$D\$500,O2))``

Snakehips
Can I use the same way for to given formulas above to make my worksheet more clean if there are no datas on the target?

Last edited:
Try something like:

=IFERROR(IF(VLOOKUP(O2,\$D:\$F,2,0)="","",VLOOKUP(O2,\$D:\$F,2,0)),"")<strike></strike>

Code:
``=COUNTIF(\$D\$3:\$D\$500,O2)``
Above formula works fine but why there are numerics ıf there is no any data from column D and E ?
O2 fourmula is
Code:
``=IF(O1="","",IFERROR(SMALL(\$D\$3:\$D\$400,COUNTIFS(\$D\$3:\$D\$400,"<="&O1)+1),""))``
What are you trying to achieve with these formulas?

Ohh achieve....
Actually my worksheet is a bit complicated with lots of exported datas from a softwear.
I created master copy which with formulas and vba codes.
Macro opens the target closed worksheet and copy/paste to master file then I get what I needed.
Hope its clear
I'm sorry if I'd bothered to you guys!

Last edited:

Replies
1
Views
276
Replies
12
Views
1K
Replies
3
Views
126
Replies
10
Views
283
Replies
2
Views
183

### Forum statistics

1,196,448
Messages
6,015,329
Members
441,889
Latest member
balolaptopgiaolong

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