Another COUNTIF question...
Honestly, I don't really know how to do any of the cold call metrics on my screenshot below.
Last Month: I want to count the number of rows where the "Last Touch" date is in the previous month. Not sure if this is even possible without having to update the formula each month.
This Month: Same as above, only current month.
Last Touches>6 Monthes Old: Count the rows that have the "Last Touch Date" more than 6 months in the past. (or 180 days is fine.)
Next Touches Overdue: Count the rows that have the "Next Touch Date" in the past.
Best Answer
-
Hi @LukeFortney
Try something like the following formulas.
Last Month: I want to count the number of rows where the "Last Touch" date is in the previous month. Not sure if this is even possible without having to update the formula each month.
=COUNTIF([Last Touch]:[Last Touch], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1)
This Month: Same as above, only current month.
=COUNTIF([Last Touch]:[Last Touch], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
Last Touches>6 Monthes Old: Count the rows that have the "Last Touch Date" more than 6 months in the past. (or 180 days is fine.)
=COUNTIF([Last Touch]:[Last Touch], <=TODAY(-180))
Next Touches Overdue: Count the rows that have the "Next Touch Date" in the past.
=COUNTIF([Next Touch]:[Next Touch], <TODAY())
Did those formulas work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi @LukeFortney
Try something like the following formulas.
Last Month: I want to count the number of rows where the "Last Touch" date is in the previous month. Not sure if this is even possible without having to update the formula each month.
=COUNTIF([Last Touch]:[Last Touch], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1)
This Month: Same as above, only current month.
=COUNTIF([Last Touch]:[Last Touch], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
Last Touches>6 Monthes Old: Count the rows that have the "Last Touch Date" more than 6 months in the past. (or 180 days is fine.)
=COUNTIF([Last Touch]:[Last Touch], <=TODAY(-180))
Next Touches Overdue: Count the rows that have the "Next Touch Date" in the past.
=COUNTIF([Next Touch]:[Next Touch], <TODAY())
Did those formulas work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Wow, amazing! I didn't even know if those formulas were possible. Thanks!!!
-
I'm always happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!