Formula To Count The Number of Days an Incident Occurred

aweber

I am building a safety event submission sheet and I wanted to add some metrics to a dashboard. If an event is submitted, that day is considered a "Red" day and if no events were submitted it is a "Green" day. The goal is to make a chart that shows number of red days out of the month.

A day will be considered red regardless of the number of events submitted on that day.

I am looking to make a chart that shows the percent of red days vs the total number of days in a month but I cant figure out a countif formula that counts the days submissions were submitted. I keep getting counts for the total number of submissions and I need a count of total number of days where submissions occurred.

Any advise?

Adam Murphy

Hi @aweber, I would try using DISTINCT to only count unique values, something like:

=COUNTIFS(DISTINCT([Date]:[Date]), MONTH(@cell ) = MONTH(TODAY()))

Hope this helps!

Frank S.

Greetings @aweber,

My initial thought would be to create a helper column, something simple like a check box that is ticked if the day is considered red. Then, you can count the days it was red, tally the number of checked days versus the total number of days in that month, and then you have your metric.

Adam Murphy

Hi @aweber, I would try using DISTINCT to only count unique values, something like:

=COUNTIFS(DISTINCT([Date]:[Date]), MONTH(@cell ) = MONTH(TODAY()))

Hope this helps!