# Counting Two different terms between dates

✭✭✭✭

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

• ✭✭✭✭✭✭

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭

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

• ✭✭✭✭✭✭

@Rachael Stammers So glad it worked for you!

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!