Glen Urquhart
Glen Urquhart ✭✭✭✭
edited 03/16/21 in Formulas and Functions

Hi All,

Hope you're keeping well.

I've recently started playing with IFERROR's and updating some existing sheets.

I have a sheet that reports with each row being a week of the year. Many of the formulas for "Rows in the future" or in some case, "Rows in the Past" return DIV0 or similar errors which I'm trying to eliminate.

Im struggling with the below formula - any recomendations / advice as to where an IFERROR should be inserted?

This formula is in the red squared cell.

For interest, the formula is used to calulate an adjusted 'rolling' sales target for a salesperson, by deducting Sales in so far for the month from their original month sales target, and dividing by the number of weeks remaining in the month.

=([Total Sales Target]35 - [Total Sales]35) / COUNTIF([Week number start date]36:[Week number start date]40, >TODAY(-7))

Any advice appreciated!

Cheers, Glen.

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Glen Urquhart

    I hope you're well and safe!

    I think Bassam missed removing the @ symbols in two places.

    Try this.

    =IFERROR([Total Sales Target]35 - [Total Sales]35 / (COUNTIF([Week number start date]36:[Week number start date]40, <TODAY(-7))), "")

    I hope that helps!

    Be safe and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!