CountIf Status Last 30 Days & Next 90 Days

Hello,

I have a sheet that has a columns that include Status, Start Date & Finish Date.

I need a countif formula that will pull back how many Completed (Status) have fallen within the last 30 days (Finish Date).

I need a countif formula that will pull back how many Not Started (Status) will fall within the next the 90 days (Start Date).

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @Traveler7777

[these] are for when you're referencing a column in the current sheet.

{these} are cross-sheet references, when you're looking at columns in a second sheet.

See: Create a Cell or Column Reference in a Formula

In your instance, it sounds like you want a COUNTIFS plural!

=COUNTIFS({NetSuite Status}, @cell = "Completed", {NetSuite Finish}, AND(@cell <= TODAY(), @cell > TODAY(-30)))

Cheers,

Genevieve

All comments

Traveler7777

I have made it this far:

How do I merge them together correctly?

=COUNTIF({NetSuite Status}, @cell = "Completed") & =COUNTIF({NetSuite Finish}, AND(@cell <= TODAY(), @cell > TODAY(-30)))

Traveler7777

Can anyone explain the [ brackets versus the { brackets?

Genevieve P.

Hi @Traveler7777

[these] are for when you're referencing a column in the current sheet.

{these} are cross-sheet references, when you're looking at columns in a second sheet.

See: Create a Cell or Column Reference in a Formula

In your instance, it sounds like you want a COUNTIFS plural!

=COUNTIFS({NetSuite Status}, @cell = "Completed", {NetSuite Finish}, AND(@cell <= TODAY(), @cell > TODAY(-30)))

Cheers,

Genevieve

Traveler7777

@Genevieve P. Thanks, can I also add if the Status is Blank?

So Status is either Completed or Cell is Blank?

=COUNTIFS({NetSuite Status}, @cell = "Completed", {NetSuite Finish}, AND(@cell <= TODAY(), @cell > TODAY(-30)))

Genevieve P.

Hi @Traveler7777

Yes! Try this:

=COUNTIFS({NetSuite Status}, OR(@cell = "Completed", @cell = ""), {NetSuite Finish}, AND(@cell <= TODAY(), @cell > TODAY(-30)))

Cheers,

Genevieve

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.