product between two dates and with criteria

[Guest]

I've been trawling the messages for a couple of hours now and am getting more
and more confused (it's my age)

I have column A with various dates
Column B has the status "open" or Closed"
I need to count how many new rows were added for each 7 day week. So all the
occurrances of anything between two dates which I have done using
=COUNTIF(range,">"&A2)-COUNTIF(range,">"&A3) Thanks guys!

but then I need to count the number in each 7 day week that are still open
and then I need to report which date has the oldest "open" item.

Please help

Stu

 

[Guest]

=SUMPRODUCT(--(date>=A2),--(date<=A3),--(status="open"))

Adjust to suit

 

[Guest]

Top quality answer thank you so much.

Two points firstly including >= and <= counts things occurring at the
changeover day twice. so deleting one = does the trick.

also I need to look down the two columns a and b and find the earliest
ocurrence of "open" in column b based on the date of col a.


does that make sense ?



Stu

 

[T. Valko]

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=MIN(IF(B1:B100="open",A1:A100))

Format as DATE

Biff

 

[Guest]

You are all Gods and your childrens children will have many fine offspring


Respect


Stu