How can I create COUNTIF for current week in current year

Ilana Trahtenberg

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

@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.

Ilana Trahtenberg

Thank you!