Alternate way of pulling Countifs with WEEKNUMBER functions



I have a weekly report that needs to count all the leads per lead source and my source sheet is pulling the data from google sheets. To count the report automatically from the sheet source, I am using this formula below. [Select week] column dictates the week number that I need. As you I have heard, WEEKNUMBER pulls Monday to Friday, but this morning my manager asked me to align this formula to our cut-off which is Saturday to Friday to tally on our reporting.

=COUNTIFS({CD1}, IFERROR(WEEKNUMBER(@cell), 0) = [Select Week]$1, {LS1}, [Lead Source]$5)

Is there a way to make the pulling of WEEKNUMBER function to Saturday to Friday?Any formula or helping columns needed? by the way source sheet is untouchable.



  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Julius Benn Sabeniano

    If you want to change the way the function WEEKNUMBER operates, you don't have much choice but go with an helper column to adapt it. Where you will have a formula like this:


    WEEKDAY returns 1 for sunday up to 7 which is saturday.

    You can also encapsulate this formula in your IFERROR function and replace your IFERROR criteria with this cell/range in your COUNTIFS.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!