| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF/PRODUCT & DATES | Excel Worksheet Functions | |||
| SUMIF/PRODUCT with multiple Criteria not working | Excel Discussion (Misc queries) | |||
| SUMPRODUCT, two criteria (date and product type) | Excel Worksheet Functions | |||
| Product of 2 arrays based on criteria | Excel Discussion (Misc queries) | |||
| how to sum using dates as a criteria | Excel Worksheet Functions | |||
Linear Mode
