Alternate way of pulling Countifs with WEEKNUMBER functions

Julius Benn Sabeniano

Hi,

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.

Capture1.PNG

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:

=IF(OR(WEEKDAY(TODAY())=7, WEEKDAY(TODAY())=1), WEEKNUMBER(TODAY())+1, WEEKNUMBER(TODAY()))

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.