Counting dates on the current week number and year


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.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!