30 Day look back

JP Pedicino

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)))

Paul Newcome

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))

Heather Duff

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.

JP Pedicino

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.

Paul Newcome

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.

JP Pedicino

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?

Paul Newcome

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))

JP Pedicino

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.

Paul Newcome

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.