Countif with Dynamic date ranges.

L

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
 
B

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)
 
T

Toppers

Try:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top