Forum Discussion

LesKing's avatar
LesKing
Brass Contributor
Jun 11, 2023
Solved

Formula to count cells with a date that is less than today

Hi, I need a formula to check column Z (which has a  date in each cell) to count how many cells in column Z have a date less than today AND where cells in column L show "Mandatory".   I have tried...
Formulas and Functions
  • SnowMan55's avatar
    SnowMan55
    Jun 11, 2023

    LesKing How about using the FILTER function (with two criteria) to get an array of data, wrapped in a COUNT function?

     

    =COUNT( FILTER(Database!Z6:Z200, (Database!Z6:Z200<TODAY()) * (Database!L6:L200="Mandatory"), "") )

     

     

    Edit: Oh, sorry, you wrote less than (before) today and I initially coded for after today.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 11, 2023

LesKing 

I do not understand why the original formula does not work!  As far as I can see

 

= COUNTIFS(
    status, "manditory", 
    date,   "<"&TODAY()
  )

 

is a perfectly valid formula.  Another would be

 

= SUM( (status = "manditory") * (date < TODAY()) )

 

Resources