product between two dates and with criteria
 

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

product between two dates and with criteria
 

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

Adjust to suit


"Stuart" wrote:

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

product between two dates and with criteria
 

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


"Teethless mama" wrote:

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

Adjust to suit


"Stuart" wrote:

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

product between two dates and with criteria
 

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

"Stuart" wrote in message
...

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


"Teethless mama" wrote:

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

Adjust to suit


"Stuart" wrote:

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

product between two dates and with criteria
 

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


Respect


Stu



"T. Valko" wrote:

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

"Stuart" wrote in message
...

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


"Teethless mama" wrote:

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

Adjust to suit


"Stuart" wrote:

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