Formula to count rows in a date range across columns and status

Elvin Montanez

I am getting suck trying to create a =COUNTIF formula from a sheet that has my date column, and several other qualifying status columns.Any advise would be greatly appreciated.  Here is where I am at so far and I am getting an #INVALID Data Type error.

=COUNTIFS({Sheet1 Range 3}, AND(@cell <= TODAY(), @cell > TODAY(-30), {Sheet1 Range 7}, <>"Complete"))

In this argument, I want to count all the rows in the last 30 days that have a status of anything other than "Complete" in column range 7.  

 

Chris McKay

Hi Elvin,

The AND is obsolete in a COUNTIFS because every range/criterion included in the statement is an AND.

Try this:

=COUNTIFS({Sheet1 Range 3}, <=TODAY(), {Sheet1 Range 3}, >TODAY(-30), {Sheet1 Range 3}, <>"Complete")

Kind regards,

Chris McKay

Elvin Montanez

Excellent. This worked.  Thanks Chris!

Chris McKay

My pleasure