Counting dates on the current week number and year
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.
Best Answer
-
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)
Answers
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!