30 Day look back
Hello,
I am using the formula below to count all instances of rows that contain "ADCVD" in my "2022 Archive RFI Reason" with my "2022 Archive Created Date Range".
My problem is that my Jan 21 data is located in in my "2021 Archive Created Date Range" sheet.
How can I modify this formula to look back 30 days in both archives?
=IF(MONTH(TODAY()) = 1, COUNTIFS({2022 Archive Created Date Range}, IFERROR(MONTH(@cell), 0) = 12, {2022 Archive Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({2022 Archive Created Date Range}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {2022 Archive Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {2022 Archive RFI Reason}, CONTAINS("ADCVD", @cell)))
Best Answer
-
Sorry. I missed grabbing that bit.
=COUNTIFS({2022 Archive Created Date Range}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), {2022 Archive RFI Reason}, CONTAINS("ADCVD", @cell)) + COUNTIFS({2021 Archive Created Date Range}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), {2021 Archive RFI Reason}, CONTAINS("ADCVD", @cell))
Answers
-
Hi @JP Pedicino ,
I may be oversimplifying it, but could it be as simple as copying the current formula, changing the range name to the 2021 one, and adding it to the 2022 formula? You may have to tweak the month/year calculations a bit, too. So (edited items in bold below):
=IF(MONTH(TODAY()) = 1, COUNTIFS({2022 Archive Created Date Range}, IFERROR(MONTH(@cell), 0) = 12, {2022 Archive Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({2022 Archive Created Date Range}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, {2022 Archive Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {2022 Archive RFI Reason}, CONTAINS("ADCVD", @cell))) + IF(MONTH(TODAY()) = 1, COUNTIFS({2021 Archive Created Date Range}, IFERROR(MONTH(@cell), 0) = 12, {2021 Archive Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({2021 Archive Created Date Range}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) + 11, {2021 Archive Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY() - 1), {2021 Archive RFI Reason}, CONTAINS("ADCVD", @cell)))
Let me know if it works.
-
Thank you, but my issue is that is has to look at both 2021 and 2022 sheets in the formula. If it finds nothing in 2021 then is needs to go to 2022.
-
My first suggestion would be to shorten your existing formula up a bit by putting the IF statement inside of the COUNTIFS instead of using two COUNTIFS. From there you would replicate the formula to point at the second sheet and then add them together.
Simplified:
=COUNTIFS({2022 Archive Created Date Range}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)))
For both sheets:
=COUNTIFS({2022 Archive Created Date Range}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0))) + COUNTIFS({2021 Archive Created Date Range}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)))
The idea behind this is that the COUNTIFS would generate a zero on one sheet and a count from the second since the data for the month you are wanting to count shouldn't be in both sheets.
-
Thank you Paul, but how about the CONTAINS portion of the formula? What I do with it since it has to look within both 2021 and 2022?
-
Sorry. I missed grabbing that bit.
=COUNTIFS({2022 Archive Created Date Range}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), {2022 Archive RFI Reason}, CONTAINS("ADCVD", @cell)) + COUNTIFS({2021 Archive Created Date Range}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), {2021 Archive RFI Reason}, CONTAINS("ADCVD", @cell))
-
Paul,
Thank you so much for your assistance!
You're absolutely correct, regarding your clean-up of the formula, it worked like a charm. I also really appreciate you taking the time to teach me a better way to approach the issue.
-
Happy to help. 👍️
If you find yourself repeating the same range/criteria sets within a formula (or in the case of IF statements the same output), then I always suggest seeing if you can consolidate somehow.
In this case, the IF is shorter than the COUNTIFS, so we can save keystrokes by repeating the IF and only doing the COUNTIFS once. Saving keystrokes means shorter formulas which (usually) means more efficient formulas. More efficient formulas can make a huge impact on sheet performance when you start getting into larger and/or more complex sheets.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!