IF Formula with Nested COUNTIFS and Last 365 Days
Hello. I am working on a formula to calculate how often an Account Number (Nominee C#) appears in a column. If it appears more than once, then a box gets checked. I have this part built and working, but now I need to add an additional parameter to only checkmark the box if they have appeared more than once in the last 365 days.
Here's what I currently have without the 365 days parameter: =IF(COUNTIFS([Nominee C#]:[Nominee C#], [Nominee C#]@row) > 1, 1, 0)
Any idea how to add that additional 365 day parameter in?
Best Answer
-
=IF(COUNTIFS([Nominee C#]:[Nominee C#], [Nominee C#]@row, Date:Date, Date@row > TODAY(-365)) > 1, "Yes", "No")
Answers
-
What is your date field that you're comparing against?
you could do this with a helper date column and a report OR if you want to do it within a formula it would be (Date:Date, @cell > TODAY(-365)) as the additional criteria
-
I had tried this before, but the formula gives me INCORRECT ARGUMENT SET. Here's my formula with that piece added in, utilizing the date we received the information for that account: =IF(COUNTIFS([Nominee C#]:[Nominee C#], [Nominee C#]@row, [Nominated Date]@row > TODAY(-365)) > 1, 1, 0)
-
=IF(COUNTIFS([Nominee C#]:[Nominee C#], [Nominee C#]@row, Date:Date, Date@row > TODAY(-365)) > 1, "Yes", "No")
-
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!