divide by zero error help

[Imran Azam]

HI guys

i have set the below formula ( with some help from mrexcel members !) below to pull some numbers but i get the #DIV/0! error sometimes do to the 0 or no data.

below is the formula

=COUNTIFS('test1'!$AC:$AC,$D7,'test1'!$V:$V,"Won",'test1'!$S:$S,">=01/05/2019",'test1'!$S:$S,"<01/06/2019")/(COUNTIFS('test1 Data'!$AC:$AC,$D7,'test1'!$V:$V,"Open")+
COUNTIFS('test1'!$AC:$AC,$D7,'Win Rate Data'!$V:$V,"Won",'test1'!$S:$S,">=01/05/2019",'test1'!$S:$S,"<01/06/2019",'test1'!$T:$T,">499")+
COUNTIFS('test1'!$AC:$AC,$D7,'test1'!$V:$V,"RED",'test1'!$S:$S,">=01/05/2019",'test1'!$S:$S,"<01/06/2019")),"-")


how can i create this so if its divde by zero it comes up witha blank rather than the error?


thanks guy

 

[tyija1995]

Nest with an IFERROR like so:

=IFERROR(COUNTIFS('test1'!$AC:$AC,$D7,'test1'!$V:$V,"Won",'test1'!$S:$S,">=01/05/2019",'test1'!$S:$S,"<01/06/2019")/(COUNTIFS('test1 Data'!$AC:$AC,$D7,'test1'!$V:$V,"Open")+
COUNTIFS('test1'!$AC:$AC,$D7,'Win Rate Data'!$V:$V,"Won",'test1'!$S:$S,">=01/05/2019",'test1'!$S:$S,"<01/06/2019",'test1'!$T:$T,">499")+
COUNTIFS('test1'!$AC:$AC,$D7,'test1'!$V:$V,"RED",'test1'!$S:$S,">=01/05/2019",'test1'!$S:$S,"<01/06/2019")),"")

 

[Imran Azam]

thanks