Counting dates on the current week number and year

Options
✭✭✭✭✭

Hi,

I am trying to count the number of New Patients (by Case Type) that visit on the current week and year. The below formula is working but for some reason I will get the error message "INCORRECT ARGUMENT" until I refresh or save. Once I do that it will calculate the correct count.

=COUNTIFS({Date of Visit}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY(), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), {New Patient}, 1, {Case Type}, \$[Primary Column]6)

Below are excerpts of the two sheets involved. The first is where the formula resides and totals up all the weekly stats. The second is the reference sheet where the data resides.

I think my formula is inefficient or incorrect and is causing the problem. I'm sure there is a much better way to write the formula.

• ✭✭✭✭✭✭
Options

I do see that you are missing an AND function and have a missing closing parenthesis after WEEKNUMBER(TODAY().

=COUNTIFS({Date of Visit}, AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), {New Patient}, 1, {Case Type}, \$[Primary Column]6)

• ✭✭✭✭✭✭