# Countif by day of week for current week

Options
✭✭✭✭✭

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

• ✭✭✭✭
edited 10/19/21
Options

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!

• ✭✭✭✭✭
Options

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!

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

It is working now! Thank you again!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!