How can I create COUNTIF for current week in current year

I am looking to count how many dates fall within the current week of the current year in crossed sheets

This the formula I am trying to use , but it does not work

=COUNTIFS({Deviation Management - Veeva [Archive] Range 3}; AND(IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()); IFERROR(YEAR(@cell); 0) = YEAR(TODAY())))


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Ilana Trahtenberg I think it's the IFERRORs that are throwing things off. Hard to tell for sure without knowing what error message you're getting.

    We can remove the IFERRORs and instead replace them with a criteria that verifies the cell is a date value before proceeding with the rest of the formula:

    =COUNTIFS({Deviation Management - Veeva [Archive] Range 3}; AND(ISDATE(@cell); WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()); YEAR(@cell) = YEAR(TODAY()))

    Why will this work? COUNTIFS works from left to right, so the first thing it will do is look for cells that are date values and disregard the rest. Then from that subset of cells that are dates, it evaluates for the weeknumber and year values - and it won't error on either of those because it's only evaluating cells that have dates in them.


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!