Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    COUNTIF Date Range with criteria

    ✭✭✭✭
    in Formulas and Functions

    I am trying to count when Brian shows up in column "Auditor Name" but only for a specific week, dating 6/3 - 6/7.

    =COUNTIFS({Audit Date}, >=DATE(2024, 6, 3), DATE(2024, 6, 7), AND({Auditor Name}, "Brian Lucas")))

    Where is this going wrong?

    image.png
    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭✭✭✭
      Answer ✓
      Joey135

      too many brackets after the AND function. Try this:

      =COUNTIFS({Audit Date}, AND(@cell >= DATE(2024, 6, 3), @cell <= DATE(2024, 6, 7)), {Auditor Name}, "Brian Lucas")

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭

      Looks like your AND() function may be misplaced. In fact, in this case, it's not needed since the criterion listed in a COUNTIFS formula defaults to "AND", requiring all criterion to be met to count unless otherwise specified.

      Try this:

      COUNTIFS({Audit Date}. >=DATE(2024, 6, 3), DATE(2024, 6, 7), {Auditor Name}, "Brian Lucas")

      · Share on FacebookShare on Twitter
    • Community Champion

      Hi @Joey135

      Try this;

      =COUNTIFS({Auditor Name}, CONTAINS("Brian", @cell), {Audit Date}, AND(@cell >= DATE(2024, 6, 3), @cell <= DATE(2024, 6, 7)))

      If you want to check "Brian Lucas", the formula would be;

      =COUNTIFS({Auditor Name}, "Brian Lucas", {Audit Date}, AND(@cell >= DATE(2024, 6, 3), @cell <= DATE(2024, 6, 7)))

      My demo solution uses a Sheet Summary field, but the structure of the COUNTIFS formula is the same;

      =COUNTIFS([Auditor Name]:[Auditor Name], CONTAINS("Brian", @cell), [Audit Date]:[Audit Date], AND(@cell >= DATE(2024, 6, 3), @cell <= DATE(2024, 6, 7)))

      Site faviconSmartsheet

      image.png
      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      jmyzk_cloudsmart_jp

      Yep, I totally missed the formatting of the date part of the formula (responded way too quick apparently). @jmyzk_cloudsmart_jp has got is spot on…

      · Share on FacebookShare on Twitter
    • Community Champion

      Hi @Joey135,

      This should do what you're after:

      =COUNTIFS({Audit Date}, AND(@cell >= DATE(2024, 6, 3), @cell <= (DATE(2024, 6, 7))), {Auditor}, "Brian Lucas")

      @brianschmidt,

      Your formula would require a couple of changes (bolded):

      =COUNTIFS({Audit Date}, >=DATE(2024, 6, 3), {Audit Date}, <=DATE(2024, 6, 7), {Auditor}, "Brian Lucas")

      It was missing a range and the equal to/less than portion.

      Hope this helps, but if you've any problems/questions just post!

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      Nick Korna

      Yep, totally missed that the first go round. Thanks

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      @Nick Korna I feel like this one should work - =COUNTIFS({Audit Date}, AND(@cell >= DATE(2024, 6, 3), @cell <= DATE(2024, 6, 7))), {Auditor Name}, "Brian Lucas")

      but I am getting UNPARSEABLE

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      Answer ✓
      Joey135

      too many brackets after the AND function. Try this:

      =COUNTIFS({Audit Date}, AND(@cell >= DATE(2024, 6, 3), @cell <= DATE(2024, 6, 7)), {Auditor Name}, "Brian Lucas")

      · Share on FacebookShare on Twitter
    • Community Champion

      It needs either the extra bracket on the second DATE (bolded):

      =COUNTIFS({Audit Date}, AND(@cell >= DATE(2024, 6, 3), @cell <= (DATE(2024, 6, 7))), {Auditor}, "Brian Lucas")

      or one less on the end of the AND:

      =COUNTIFS({Audit Date}, AND(@cell >= DATE(2024, 6, 3), @cell <= DATE(2024, 6, 7)), {Auditor}, "Brian Lucas")

      Whichever you prefer!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions