Countifs for today calculation
Hi there! I'm trying to bring items from multiple sheets to populate a metric sheets. Basically, I would need a hand to bring the following data to count how many check box items have been modified on the last 7 days and more than 7 days. This check box row items can be level 2 or 3, the column name is Level.
Count ifs last 7 days:
Checkbox is marked, on level 2 and 3 rows. (Hierarchy)
if modified is on the last 7 days from today.
Count ifs more than 7 days:
Checkbox is marked, on level 2 and 3 rows. (Hierarchy)
if modified is more than 7 days from today
Thank you!
Answers
-
Hello @Jbro,
You can use the TODAY() function for both, I don't know if you want them combined into 1 formula or not, here are the 3 different ones you can try.
=COUNTIFS(Modified@row, =TODAY(-7), Hierarchy@row, =1)
=COUNTIFS(Modified@row, <TODAY(-7), Hierarchy@row, =1)
Combined:
=COUNTIFS(Modified@row, <=TODAY(-7), Hierarchy@row, =1)
-
Hi Erik, can the Hierarchy work if I am sourcing from another sheet? Sorry forgot to mention that detail.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!