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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!