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

kss5229

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?

Nic Larsen

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

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

Nic Larsen

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

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

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