Counting Two different terms between dates

Rachael Stammers

Hi,

I am trying to count the number of times term "Incident" OR the term "Near Miss" appear between two dates, but my formula is giving me the incorrect count. The formula I am using is:

=COUNTIFS({2. Initial Safety Review Range 2}, >=DATE(2023, 1, 1), {2. Initial Safety Review Range 2}, <=DATE(2023, 1, 31), {2. Initial Safety Review Range 1}, "Incident", {2. Initial Safety Review Range 1}, "Near Miss")

Where:

2. Initial Safety Review Range 2 is a date column

2. Initial Safety Review Range 1 Is a dropdown column with three terms "Hazard" "Incident" and "Near Miss".

Thank you

Rachael

Jeff Reisman

@Rachael Stammers You are close!

Within the COUNTIFS function is an implied "AND", meaning all the parameters must be true for it to count. So it's looking for rows that have both "Incident" and "Near Miss" in Range 1. To counteract that, you need to insert an OR when wanting to count either of two values in a range:

=COUNTIFS({2. Initial Safety Review Range 2}, >=DATE(2023, 1, 1), {2. Initial Safety Review Range 2}, <=DATE(2023, 1, 31), {2. Initial Safety Review Range 1}, OR(@cell = "Incident", @cell = "Near Miss"))

Alternatively, you could count all the rows with Incident, then count all the rows with Near Miss, and add them together:

=COUNTIFS({2. Initial Safety Review Range 2}, >=DATE(2023, 1, 1), {2. Initial Safety Review Range 2}, <=DATE(2023, 1, 31), {2. Initial Safety Review Range 1}, "Incident") + COUNTIFS({2. Initial Safety Review Range 2}, >=DATE(2023, 1, 1), {2. Initial Safety Review Range 2}, <=DATE(2023, 1, 31), {2. Initial Safety Review Range 1}, "Near Miss")