# IFERROR Formula

Options
✭✭✭✭
edited 03/16/21

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

Cheers, Glen.

Tags:

• ✭✭✭✭✭✭
Options

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!

Best,

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

• ✭✭✭✭✭✭
edited 03/17/21
Options

Hi @Glen Urquhart

Hope you are fine, please try the following formula:

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

bassam.khalil2009@gmail.com

• ✭✭✭✭
Options

Hi Bassam,

Thats the format that I've used for all other columns, sandwiching the original formula with the IFERROR, but unfortunatly its #UNPARSEABLE for this one.

(I just double checked using your version too)

Thank you for your help anyway!

Cheers,

Glen.

• ✭✭✭✭✭✭
Options

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!

Best,

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

• ✭✭✭✭
Options

Thank you for commenting Andrée.

This version was working, but not giving the answer I'd hoped for...

In typing my response I also noticed another error I had put in last night meaning the math wasn't following the route I'd expected - I'd missed out a couple of brackets.

=IFERROR(([Total Sales Target]35 - [Total Sales]35) / (COUNTIF([Week number end date]36:[Week number end date]40, >=TODAY())), "Month Concluded")

The final formula is as above, and this works as it should.

Thank you both for your help, hope you have a great day :)

Cheers, Glen.

• ✭✭✭✭✭✭
Options

Excellent!

You're more than welcome!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | 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!