Forum Discussion
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 various formulae without success, most of which are similar to this one below;
=COUNTIFS(DATABASE!L6:L200,"Mandatory",DATABASE!Z6:Z200,"<"&TODAY())
but none of them work. Can anyone point me in the right direction please?
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.
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()) )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.
- Hi SnowMan55 - that worked perfectly - I am very grateful for your help!
Les King
