product between two dates and with criteria

S

Stuart

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
 
S

Stuart

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

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
 
S

Stuart

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


Respect


Stu
 
Top