This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have this formula in my P&L report that works where we have Sales and Costs for the month. If there are no sales AND no costs it will result in #NUM! and where there are no sales OR no costs it will result in #DIV/0!. Is there a similar formula to IFERROR where I can say if the formula results in an error, give "0".
=Sum(IIf([Grouping3]="2.Controllable Costs",[TY Mth],0))/-Sum(IIf([Grouping2]="01.Net Sales",[TY Mth],0))
I have tried different variants of Nz( ), IIf(IsError( ) and IIf(<formula>=0,0,<formula>) and they haven't worked.
Thank you,
Chantelle
A family of Microsoft relational database management systems designed for ease of use.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
=IIf(Sum(IIf([Grouping2]="01.Net Sales",[TY Mth],0))=0,0,Sum(IIf([Grouping3]="2.Controllable Costs",[TY Mth],0))/-Sum(IIf([Grouping2]="01.Net Sales",[TY Mth],0)))
=IIf(Sum(IIf([Grouping2]="01.Net Sales",[TY Mth],0))=0,0,Sum(IIf([Grouping3]="2.Controllable Costs",[TY Mth],0))/-Sum(IIf([Grouping2]="01.Net Sales",[TY Mth],0)))
That worked a charm - thank you!