Count if date is in the last six months
I'm trying to refine my metrics for the number of Packages archived in the last year. This formula (which is working correctly across two sheets)...
=COUNTIFS({Package Status 1}, CONTAINS("Archived", @cell), {Package Type 1}, "HWMP") + COUNTIFS({Package Status 2}, CONTAINS("Archived", @cell), {Package Type 2}, "HWMP")
...would be narrowed to only count the HWMP-type packages in sheet 1 with an Archived status in the last year plus the HWMP-type packages in sheet 2 with an Archived status in the last year. This formula returns #INVALID OPERATION
=COUNTIFS({Package Status 1}, CONTAINS("Archived", @cell), {Package Type 1}, "HWMP",TODAY() - {Final Archive Date 5} < 365) + COUNTIFS({Package Status 2}, CONTAINS("Archived", @cell), {Package Type 2}, "HWMP",TODAY() - {Archive Date} < 365)
Best Answer
-
Try:
=COUNTIFS({Package Status 1}, CONTAINS("Archived", @cell), {Package Type 1}, "HWMP",{Final Archive Date 5}, @cell> today(-365)) + COUNTIFS({Package Status 2}, CONTAINS("Archived", @cell), {Package Type 2}, "HWMP", {Archive Date}, @cell > today(-365))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Try:
=COUNTIFS({Package Status 1}, CONTAINS("Archived", @cell), {Package Type 1}, "HWMP",{Final Archive Date 5}, @cell> today(-365)) + COUNTIFS({Package Status 2}, CONTAINS("Archived", @cell), {Package Type 2}, "HWMP", {Archive Date}, @cell > today(-365))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Gabby,
As I understand it, the COUNTIFS function looks for both a range (R) and criterion (C).
So, the second formula above has:
R1 of {Package Status 1}
C1 of CONTAINS("ARCHIVED", @cell)
R2 of {Package Type 1}
C2 of "HWMP"
R3 of TODAY() - {Final Archive Date 5} < 365 (which I'm assuming should be the criterion?)
C3 of ?
I'm wondering if it should read
R3 of {Final Archive Date 5}
C3 of ( TODAY() - {Final Archive Date 5} ) <365
and same again for the second COUNTIFS function in the formula?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
Thanks, @Mark Cronk and @Jason Albrecht ! Adding {Archive Date}, @cell > today(-365) to the string for each sheet worked! For whatever reason, the two suggestions didn't provide the same result.
-
Glad you found a solution. Thank you for contributing to the community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
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!