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

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

Rachael Stammers

Thank you @Jeff Reisman this worked perfectly and just what I needed. I knew it was thinking my formula meant AND, but I had no idea how to write it to be OR. Really appreciate your assistance with this.

Rachael

Jeff Reisman

@Rachael Stammers So glad it worked for you!