How to find % of events that meet specific conditions within certain time window

Options

In my Smartsheet, I have a column for Total # of Events Per Month. I am trying to find a way to calculate the Percent Events Closed Per Month.

For the first part, I'm trying to calculate the # of events closed per month. Each row has a month start date & end date listed. Events that are closed have a "Conclusion" drop down selection of either "Error Confirmed" or "No further Investigation Required". If the event occurred within the start & end date, and the conclusions are either "Error Confirmed" or "No further Investigation Required", I want it to count up 1.

Here is the formula I'm using so far, but I am getting an "#INVALID DATA TYPE" error:

=COUNTIFS({Date of Occurrence}, >=[Start Date]4, {Date of Occurrence}, <=[End Date]4, OR({Conclusions}, ="Error Confirmed", {Conclusions}, ="No Further Investigation Required"))

Once I get this count #, I would like to get the % closed per month by dividing this number by the Total # of Events Per Month. Is it possible to do this all within 1 column or do I need separate columns for # of events closed per month and % closed per month?

Best Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this to see if you can get the formula working:

    =COUNTIFS({Date of Occurrence}, >= [Start Date]@row, {Date of Occurrence}, <= [End Date]@row, {Conclusions}, OR(@cell ="Error Confirmed", @cell = "No Further Investigation Required")

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓
    Options

    You can. I tend to use a couple columns to confirm both formulas are working before joining them. But after the formula, leave a space then / and add your next formula. Here is an example of two calculations happening:

    =SUM([Week 1]:[Week 1]) * ID1 + SUM([Time Log ID]:[Time Log ID])

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this to see if you can get the formula working:

    =COUNTIFS({Date of Occurrence}, >= [Start Date]@row, {Date of Occurrence}, <= [End Date]@row, {Conclusions}, OR(@cell ="Error Confirmed", @cell = "No Further Investigation Required")

  • kss5229
    kss5229 ✭✭
    Options

    I think that worked! Am I able to do the % calculation within that same column or do I need a separate column for that?

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓
    Options

    You can. I tend to use a couple columns to confirm both formulas are working before joining them. But after the formula, leave a space then / and add your next formula. Here is an example of two calculations happening:

    =SUM([Week 1]:[Week 1]) * ID1 + SUM([Time Log ID]:[Time Log ID])

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!