COUNTIFS Formula for % Complete with Dates and Check Boxes

Options

We are trying to create a formula to calculate percentage complete based on specific criteria met in multiple columns within a Sheet Summary to build a Dashboard.

The formula we created is as follows:  =(COUNTIFS([45-Day Check-in Mark]:[45-Day Check-in Mark], <TODAY(), [45-Day Check-in Complete]:[45-Day Check-in Complete], =1, [45-Day Check-in Mark]:[45-Day Check-in Mark], <>"")) / (COUNT([Training End Date]:[Training End Date]))

Our goal is for the formula to calculate percentage complete for onboarding check-ins at Two Weeks, 45-days, and more! We however, need to the % complete to not include in the total when no date is listed a column and the check box in another column is unchecked.

Answers

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 10/13/23
    Options

    it looks like your current formula is correct, but you would need to add an IF statement before to check for whether date is not present, it also looks like you might need to nest the IF, or IF(AND, to check for the checkbox + date prior to the COUNT functions.

    Sincerely,

    Jacob Stey

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!