Using COUNTIFS with OR and CONTAINS

Lee Unger

Hi All,

I'm trying to create a formula to count how many requests of a specific type my team handled on a given week. I have the table set up like this:

in the column of insights decks, I would like the formula to count how many times a request was put in that week, that has either Insights, Screenshots, or Campaign in the names (The original names are Standard Insights Requests, Quick Screenshots (no write up), Post Demo Screenshots, Quick Screenshots (Include write up), Post Campaign Report. I'm doing Contains instead of just OR with the exact text since the options that have parentheses in the names messed up the formula).

Here's what I've got based on some threads I found here:

=COUNTIFS({Request Format}, OR(CONTAINS("Insights", @cell), CONTAINS("Screenshots", @cell), CONTAINS("Campaign", @cell)), {Calender Week}, [Column2]@row).

Unfortunately, it brings back zero as can be seen in the screenshot above, even though I have the numbers it should bring back and it's definitely not zero. Any idea what I'm doing wrong here?

Thank you!

Lee Unger

Thank you Jeff! I think I figured it out, the dates were 2022 instead of 2021 :) Once I changed that it worked perfectly.

Jeff Reisman

You may need an operator for the {Calendar Week} criteria:

=COUNTIFS({Request Format}, OR(CONTAINS("Insights", @cell), CONTAINS("Screenshots", @cell), CONTAINS("Campaign", @cell)), {Calender Week}, =[Column2]@row)

Lee Unger

Thank you Jeff! I think I figured it out, the dates were 2022 instead of 2021 :) Once I changed that it worked perfectly.