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)

Find more posts tagged with

Formulas

Accepted answers

Mark Cronk

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

All comments

Mark Cronk

Try:

Mark

Jason Albrecht

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?

Gabby Nepomuceno

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.

Mark Cronk

Glad you found a solution. Thank you for contributing to the community.

Mark

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.