Countif with Dynamic date ranges.

[Leonhardtk]

I have a worksheet *(sheeta) with a list of activities with "Open" and
"Closed" dates.

In worksheet B, I have A2:A50 I have
A B
1 =TODAY()-7 =COUNTIF('sheeta'!A1:A50,">=" &'sheetb'!A1)
2 =TODAY()-14 ={number of occurances between A2 and A1}
3 =TODAY()-21 ={number of occurances between A3 and A2}
4 etc....

In Colum B1, I do a COUNTIF('sheeta'!A1:A50,">=" &'sheetb'!A1)

In B2:B50, I want to do =COUNTIF('sheeta'!A1:A50,">=" &'sheetb'!A2 AND "<"
&'sheetb'!A1)

THe idea is to found out, by week (each day the start date will change to
TODAY()-7.)
Each day.

Any ideas?

KSL

 

[Bob Phillips]

B2: =COUNTIF('sheeta'!A1:A50,">=" &'sheetb'!A2)-SUM($B$1:$B1)

and copy down


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Toppers]

Try:

=COUNTIF(sheeta!A1:A50,">=" &sheetb!A2)- COUNTIF(sheeta!A1:A50,">="
&sheetb!A1)