Counting rows from another sheet that meet a specific criteria

crokicki

Hi All!

I'm trying to count the amount of rows from another sheet that meet the following criteria:

All rows that are within the "Quality Control - Bioassay" department, that are not closed nor cancelled in the status column and the creation date is less than 30 days, then less than 60 days and then more than 90 days

=COUNTIFS(AND({Dev Department}@row = "Quality Control - Bioassay", NOT(CONTAINS("Closed", {Dev Status}@row)), NOT(CONTAINS("Cancelled",{Dev Status}@row))), <=30)

Not sure what I'm doing wrong - thank you!!

Paul Newcome

Looks like you have a few syntax issues.

=COUNTIFS({Dev Department}, @cell = "Quality Control - Bioassay", {Dev Status}, AND(@cell <> "Closed", @cell <> "Cancelled"), {Date Range}, @cell<= TODAY(30))

Paul Newcome

Looks like you have a few syntax issues.

=COUNTIFS({Dev Department}, @cell = "Quality Control - Bioassay", {Dev Status}, AND(@cell <> "Closed", @cell <> "Cancelled"), {Date Range}, @cell<= TODAY(30))

crokicki

@Paul Newcome - That worked! Thank you!

I kind of messed up what I was asking for additionally - but what if I want to find the amount

Have been opened between 30-60 days since their creation date? @cell>TODAY(30), cell <= TODAY(60)?

Have been opened for over 90 days since their creation date? @cell > TODAY(90)?

Paul Newcome

Between thirty and sixty days would require another AND statement similar to the AND statement used for the Status. More than 90 would be the same as the formula in my last comment with just a change to the argument/criteria for the date range.

crokicki

@Paul Newcome - That makes sense. My one problem is that I'm trying to see if "Today" is more than 90 days past the creation date; Not "90 days in the future from Today"

=COUNTIFS({Dev Department}, @cell = "Quality Control - Chemistry", {Dev Status}, AND(@cell <> "Closed", @cell <> "Cancelled"), {Dev Creation Date}+ 90, @cell > TODAY())

Paul Newcome

Your syntax is still off. The 90 goes inside of the TODAY function the same way the number is in the last formula I provided and how you have it in your comment from yesterday.