COUNTIF? (Count number of date values in a column more than 30 days old)

Hello,

I'm working with a spreadsheet which tracks the status of various projects over a date range. New projects come in and are automatically assigned daily. I want to set several cells within the spreadsheet to tell me:

1. How many total tasks there are. I already have this part working, with:

PHP Code: 

=COUNT($F:$F) 


2. How many tasks are overdue. I've tried:

PHP Code: 

=COUNTIF($F:$F,TODAY()-$F2>=30) 


Strangely, this doesn't do anything.

3. How many tasks are new since the prior workday. I have tried:

PHP Code: 

=COUNTIF($F:$F,IF(WEEKDAY(TODAY())=2,TODAY()-$F1<=3,TODAY()-$F1<=1)) 


Modeled this after conditional formatting I'm using on the same column, and this also does not work.

Thanks.

Maybe these?

=SUMPRODUCT(--(TODAY()-$F2:$F$100>=30))

=SUMPRODUCT(--(TODAY()-$F1:$F$100<=3),--(TODAY()-$F1:$F$100>=1))

using defined ranges instead of whole ranges is more effiecient....

Originally Posted by NBVC

Maybe these?

=SUMPRODUCT(--(TODAY()-$F2:$F$100>=30))

=SUMPRODUCT(--(TODAY()-$F1:$F$100<=3),--(TODAY()-$F1:$F$100>=1))

using defined ranges instead of whole ranges is more effiecient....

When I copy your formulas into a cell in my sheet, the first one returns the value "99" and does not change when I add date values within the intended range. However, when I add FUTURE dates, it starts counting down from 99... not the desired functionality here.

The second formula gives a #value! error, which I can't debug.

In any case, I see two probable issues: one, I'm using $F:$F because my data fluctuates between 0 and 1000 values, but can exceed that number... in theory I could have hundreds of thousands of values, though if that ever happens someone else will be doing my job.
Secondly, I'm not sure what Sumproduct actually does. I'm using countif because I want to determine how many tasks there are, and I know CountIF counts cells with numeric values within the range specified (and I thought, based on conditions set for it.) I don't want it to SUM my dates within the range, I want it to count the number of cells containing date values.

Originally Posted by Bob Phillips

2. =countif($f:$f,"<="&today()-30)

3. =countif($f:$f,today()-if(weekday(today())=2,3,1))

Your 2. works great, thank you.

Your 3. Returns 0 no matter what values I put in the cells. I changed weekday(today())=5, so that it would accurately reflect the conditions of today (actual) for testing purposes, rather than the intended use of the formula (which would detect Mondays and count incoming tasks since Friday)

Yeah, I think Bob understood your request better than I....

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread