# CountIf Status Last 30 Days & Next 90 Days

Options
✭✭✭

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

Tags:

• Employee
Options

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

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭
edited 11/18/21
Options

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

• ✭✭✭
edited 11/18/21
Options

Can anyone explain the [ brackets versus the { brackets?

• Employee
Options

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

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭
Options

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

• Employee
Options

Yes! Try this:

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

Cheers,

Genevieve