COUNTIF Date Range with criteria

Joey135

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?

brianschmidt

https://community.smartsheet.com/discussion/comment/441308#Comment_441308

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

brianschmidt

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

jmyzk_cloudsmart_jp

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

https://app.smartsheet.com/b/publish?EQBCT=68c284994cc74247bb3b5e3c516575dd

brianschmidt

https://community.smartsheet.com/discussion/comment/441301#Comment_441301

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…

Nick Korna

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!

brianschmidt

https://community.smartsheet.com/discussion/comment/441306#Comment_441306

Yep, totally missed that the first go round. Thanks

Joey135

@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

brianschmidt

https://community.smartsheet.com/discussion/comment/441308#Comment_441308

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

Nick Korna

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!