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?
Best Answer
-
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")
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")
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!