Help with #div/0! error in IF formula

[Eastbay2]

Here is my current formula :

=SUM(IF($C232=data!$D:$D,IF('Combined Stores'!E$6=data!$H:$H,data!$F:$F,0),0))/SUM(IF($C232=data!$D:$D,IF('Combined Stores'!E$6=data!$H:$H,data!$E:$E,0),0))*100

The issue is that when referencing "data!$D:$D" & "data!$H:$H" sometime there is no data, resulting in the zero. The formula then divided by zero causing a #div/0! result. I want it to leave the value as zero if there is no refernce data.

Thanks for your help.

 

[Matt Rogers]

Hi.

Try:
=if(SUM(IF($C232=data!$D:$D,IF('CombinedStores'!E$6=data!$H:$H,data!$F:$F,0),0))=0,"",SUM(IF($C232=data!$D:$D,IF('Combi...etc your formula ...

 

[Aladin Akyurek]

Here is my current formula :

=SUM(IF($C232=data!$D:$D,IF('Combined Stores'!E$6=data!$H:$H,data!$F:$F,0),0))/SUM(IF($C232=data!$D:$D,IF('Combined Stores'!E$6=data!$H:$H,data!$E:$E,0),0))*100

The issue is that when referencing "data!$D:$D" & "data!$H:$H" sometime there is no data, resulting in the zero. The formula then divided by zero causing a #div/0! result. I want it to leave the value as zero if there is no refernce data.

Thanks for your help.

Why not wrap the formula with an IFERROR call...

=IFERROR(SUM(IF($C232=data!$D:$D,IF('Combined Stores'!E$6=data!$H:$H,data!$F:$F,0),0))/SUM(IF($C232=data!$D:$D,IF('Combined Stores'!E$6=data!$H:$H,data!$E:$E,0),0))*100,0)

 

[Eastbay2]

Thank you Aladin!!

That worked. I was trying to use the "Iferror" before i posted for help but i couldnt figure it out. I was forgetting the ",0" after the 100, duhhh!!. Thanks again.

 

[Aladin Akyurek]

Thank you Aladin!!

That worked. I was trying to use the "Iferror" before i posted for help but i couldnt figure it out. I was forgetting the ",0" after the 100, duhhh!!. Thanks again.

You are welcome. Thanks for providing feedback.