CountIf Status Last 30 Days & Next 90 Days

Traveler7777

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

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

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