CountIfs with Multiple Criteria including Dates

AGK

I am trying to count a monthly total of submissions in a given category, in this case "survey". There are three subcategories (below) and they live in the Request Type reference range. What am I missing? I keep getting a zero when I should be getting 11.

The below Countifs fx works without the "({Request Type}, "Surveys - Custom Cut", {Request Type}, ="Surveys - Analysis/Investigation", {Request Type}, ="Surveys - New Custom Survey" part of the syntax but I only want the count of these specific subcategories

=COUNTIFS({Request Type}, "Surveys - Custom Cut", {Request Type}, "Surveys - Analysis/Investigation", {Request Type}, "Surveys - New Custom Survey", {Requested On}, >=DATE(2022, 8, 31), {Requested On}, <=DATE(2022, 10, 1))

Paul Newcome

When you follow the standard syntax of a COUNTIFS, each range/criteria group has an implied "AND" in between. So basically your formula is looking for a cell that equals exactly "Surveys - Custom Cut" and exactly "Surveys - Analysis/Investigation" and exactly "Surveys - New Custom Survey" all at the same time which just isn't possible.

You will need to work in an OR statement like so:

=COUNTIFS({Request Type}, OR(@cell = "Surveys - Custom Cut", @cell = "Surveys - Analysis/Investigation", @cell = "Surveys - New Custom Survey"), {Requested On}, >=DATE(2022, 8, 31), {Requested On}, <=DATE(2022, 10, 1))

AGK

Thanks Paul! I had found this solution and had tried it...makes complete sense....was getting an #UNPARSEABLE, but my () were in the wrong spots. You rock per usual! :)

Paul Newcome

When you follow the standard syntax of a COUNTIFS, each range/criteria group has an implied "AND" in between. So basically your formula is looking for a cell that equals exactly "Surveys - Custom Cut" and exactly "Surveys - Analysis/Investigation" and exactly "Surveys - New Custom Survey" all at the same time which just isn't possible.

You will need to work in an OR statement like so:

=COUNTIFS({Request Type}, OR(@cell = "Surveys - Custom Cut", @cell = "Surveys - Analysis/Investigation", @cell = "Surveys - New Custom Survey"), {Requested On}, >=DATE(2022, 8, 31), {Requested On}, <=DATE(2022, 10, 1))

AGK

Thanks Paul! I had found this solution and had tried it...makes complete sense....was getting an #UNPARSEABLE, but my () were in the wrong spots. You rock per usual! :)

Paul Newcome

Happy to help. 👍️