Counting dates on the current week number and year

Sam Lugiano

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.

Your help is much appreciated.

Paul Newcome

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)

Paul Newcome

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)