Formula Help - Count If (percentage complete)

I'm looking help on a formula to find the "Percent Finished", in row 1 column 1, by counting the total number of "Test Status" entries of: "Completed, Previously Enrolled, Enrollment Not Needed, Client Did Not Approve" and Dividing it by the count of "SAP ID"s.

Thanks!

Mary F

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Mary Farmer

    Looks like you're just missing closing off that OR statement before you use the / operator!

    Here's the structure of all the parentheses:

    =COUNTIFS(xxx, OR(xxx)) / COUNT(xxx)

    Try:

    =COUNTIFS([Test Status]:[Test Status], OR(@cell = "Completed", @cell = "Previously Enrolled", @cell = "Enrollment Not Needed", @cell = "Client Did Not Approve")) / COUNT([SAP ID]:[SAP ID])

    Cheers,

    Genevieve

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this...

    =COUNTIFS([Test Status]:[Test Status], OR(@cell = "Completed", @cell = "Previously Enrolled", @cell = "Enrollment Not Needed", @cell = "Client Did Not Approve") / COUNT([SAP ID]:[SAP ID])

  • Mary Farmer
    Mary Farmer ✭✭✭✭✭

    I typed this one in & got an #UNPARSEABLE error:

    =COUNTIFS([Test Status]:[Test Status], OR((@cell = "Completed", @cell = "Previously Enrolled", @cell = "Enrollment Not Needed", @cell = "Client Did Not Approve") / COUNT([SAP ID]:[SAP ID])

    I pasted this one in & got an #INVALID OPERATION error:

    =COUNTIFS([Test Status]:[Test Status], OR(@cell = "Completed", @cell = "Previously Enrolled", @cell = "Enrollment Not Needed", @cell = "Client Did Not Approve") / COUNT([SAP ID]:[SAP ID]))

    Thoughts?

    Thanks,

    Mary

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Mary Farmer

    Looks like you're just missing closing off that OR statement before you use the / operator!

    Here's the structure of all the parentheses:

    =COUNTIFS(xxx, OR(xxx)) / COUNT(xxx)

    Try:

    =COUNTIFS([Test Status]:[Test Status], OR(@cell = "Completed", @cell = "Previously Enrolled", @cell = "Enrollment Not Needed", @cell = "Client Did Not Approve")) / COUNT([SAP ID]:[SAP ID])

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!