How to apply sheet summary calculations while excluding error values?


I'm trying to create On Time Delivery (OTD) metrics using sheet summaries. These work well on the test sheet, even with blanks and missing components, yet when applied to live project sheets the returned values contain #invalid data type for any blank entries or those with a missing component which ultimately blocks the sheet summary calculations.

I've tried to research solutions using CONTAINS, ISDATE, IFERROR but can't find the right application or where to place them.

OTD % formula:

=COUNTIF([Final Variance]:[Final Variance], >=0) / (COUNT([Final Variance]:[Final Variance]))

I don't know how to prevent/exclude cells that return #invalid data type so my sheet summary report consists of the correct % for the test sheet, then a bunch of #invalid data types below that

The final variance formula itself is below but might in fact be the root problem needing some attention:

=DATEONLY([Final delivery]@row) - ([FE: final files delivered]@row)

Thank you in advance for this specific issue and I'm open to any other methods on OTD calculation across hundreds of sheets!

Best Answers

  • C. Powell
    C. Powell ✭✭✭
    Answer ✓

    Hi Genevieve,

    Both worked, thank you!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!