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