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?
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)))
https://app.smartsheet.com/b/publish?EQBCT=68c284994cc74247bb3b5e3c516575dd
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")
@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!
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
It needs either the extra bracket on the second DATE (bolded):
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!
ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.
