# COUNTIF Date Range with criteria

✭✭✭✭

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?

Tags:

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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…

• ✭✭✭✭✭✭

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

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!

• ✭✭✭✭✭✭

Yep, totally missed that the first go round. Thanks

• ✭✭✭✭

@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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!