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

04/30/18 Edited 12/09/19

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

  • Excellent. This worked.  Thanks Chris!

  • Chris McKayChris McKay ✭✭✭✭✭

    My pleasure

Sign In or Register to comment.