Countif by day of week for current week

I am trying to create a formula that brings in the totals by day of week (Monday, Tuesday, etc.) for each employee for the current week. The formula I have below is working but brining in all the Tuesdays from the whole sheet and not just those with "Submitted Dates" of this week.

I have a helper column ("Day of Week") on the reference sheet that lists the day of the week for each row.

=COUNTIFS({day of week}, "Tuesday", {Assigned to}, "Kendra Hillmer", {Submitted Date}, IFERROR(WEEKDAY(@cell), 0) = WEEKDAY(TODAY()))

Thank you for your help!

Find more posts tagged with

Accepted answers

All comments

Javed Hassan

Hello!

Based on your description, it sounds like you need to use the WEEKNUMBER function. See below:

=COUNTIFS({Day of Week}, "Tuesday", {Assigned To}, "Kendra Hillmer", {Submitted Date}, IFERROR(WEEKDAY(@cell), 0) = WEEKDAY(TODAY()), {Submitted Date}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))

If you want to make sure you can use this year over year, I would also add a "YEAR" function at the end.

Hope this helps!

Kendra Hillmer

Thank you for your help!

I just tried it with your updated formula and it is still bringing back "0" when there are multiple "Tuesdays" on that sheet for Kendra for this week.

Am I missing something else?

Thank you again for your time!

Javed Hassan

One thing I've tried before to troubleshoot is make the 0's text like "Fail weekday" or "Fail weeknum". this may help you identify which part of the formula is throwing the error. I tested this formula on a sample sheet and was able to get it to function, so my guess is one of these is throwing an error, possibly as a result of the date field.

One last thought I have is if submitted date is the system's "Created date", changing it to DATEONLY may help. I've had mixed success with using that function when having date issues.

Kendra Hillmer

Thank you so much for your help! I will try that now!

Kendra Hillmer

It is working now! Thank you again!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.