CountIfs with Multiple Criteria including Dates


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

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    AGK ✭✭
    Answer ✓

    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! :)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!