| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Difficult but do-able?
In a sheet in which I keep track of questions coming in, I not the date in
and date closed. in another sheet I want to check how many questions were raised in a month and how many closed in the same month. Each question has its own line. How can I best tackle this? -- ** Fool on the hill ** |
|
#2
|
|||
|
|||
|
If on sheet1 you have these columns:
A: question B: date in (format as date) C: date closed (format as date) and on sheet2 A: months (format as number) B: questions raised C: question closed in month of raising Then in sheet2 B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2)) C2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2),--(MONTH(Sheet1!C$2:C$9)=A2)) Regards, Stefi "Jaydubs" wrote: In a sheet in which I keep track of questions coming in, I not the date in and date closed. in another sheet I want to check how many questions were raised in a month and how many closed in the same month. Each question has its own line. How can I best tackle this? -- ** Fool on the hill ** |
|
#3
|
|||
|
|||
|
Great thanks very much for this super answer.
This is what I was looking for, but now I see that some of the questions are carried over to the next month........For instance a question is raised on the last day of the month and answered on the first day of the next month, how do I take this into consideration?? As these will not appear in the formula given by you ! -- ** Fool on the hill ** "Stefi" wrote: If on sheet1 you have these columns: A: question B: date in (format as date) C: date closed (format as date) and on sheet2 A: months (format as number) B: questions raised C: question closed in month of raising Then in sheet2 B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2)) C2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2),--(MONTH(Sheet1!C$2:C$9)=A2)) Regards, Stefi "Jaydubs" wrote: In a sheet in which I keep track of questions coming in, I not the date in and date closed. in another sheet I want to check how many questions were raised in a month and how many closed in the same month. Each question has its own line. How can I best tackle this? -- ** Fool on the hill ** |
|
#4
|
|||
|
|||
|
Hi
Formula 2 given to you by Stefi, gives the number raised and closed in the same month, which, as you rightly point out will omit those raised lat month, but closed this month. Either, change formula 2 to take out the test for month raised e.g. C2: =SUMPRODUCT(--(MONTH(Sheet1!C$2:C$9)=A2)) or leave C2 as it is, and have another category in D2 for those closed but not raised in same period which would be D2: =SUMPRODUCT(--(MONTH(Sheet1!C$2:C$9)=A2)) - C2 Regards Roger Govier Jaydubs wrote: Great thanks very much for this super answer. This is what I was looking for, but now I see that some of the questions are carried over to the next month........For instance a question is raised on the last day of the month and answered on the first day of the next month, how do I take this into consideration?? As these will not appear in the formula given by you ! |
|
#5
|
|||
|
|||
|
Hello Roger,
Great, This solves my question. Thank you very much !! Kind regards, Jay -- ** Fool on the hill ** "Roger Govier" wrote: Hi Formula 2 given to you by Stefi, gives the number raised and closed in the same month, which, as you rightly point out will omit those raised lat month, but closed this month. Either, change formula 2 to take out the test for month raised e.g. C2: =SUMPRODUCT(--(MONTH(Sheet1!C$2:C$9)=A2)) or leave C2 as it is, and have another category in D2 for those closed but not raised in same period which would be D2: =SUMPRODUCT(--(MONTH(Sheet1!C$2:C$9)=A2)) - C2 Regards Roger Govier Jaydubs wrote: Great thanks very much for this super answer. This is what I was looking for, but now I see that some of the questions are carried over to the next month........For instance a question is raised on the last day of the month and answered on the first day of the next month, how do I take this into consideration?? As these will not appear in the formula given by you ! |
|
#6
|
|||
|
|||
|
My solution is an answer exactly to what you asked. If I were you I would
measure the elapsed time from date in to date closed: Then in sheet2 B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2)) 'This many questions were raised in the month C2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A3),--(Sheet1!C2:C9-Sheet1!B$2:B$9<=30)) 'From B2 this many questions were closed within 30 days Regards, Stefi "Jaydubs" wrote: Great thanks very much for this super answer. This is what I was looking for, but now I see that some of the questions are carried over to the next month........For instance a question is raised on the last day of the month and answered on the first day of the next month, how do I take this into consideration?? As these will not appear in the formula given by you ! -- ** Fool on the hill ** "Stefi" wrote: If on sheet1 you have these columns: A: question B: date in (format as date) C: date closed (format as date) and on sheet2 A: months (format as number) B: questions raised C: question closed in month of raising Then in sheet2 B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2)) C2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2),--(MONTH(Sheet1!C$2:C$9)=A2)) Regards, Stefi "Jaydubs" wrote: In a sheet in which I keep track of questions coming in, I not the date in and date closed. in another sheet I want to check how many questions were raised in a month and how many closed in the same month. Each question has its own line. How can I best tackle this? -- ** Fool on the hill ** |
|
#7
|
|||
|
|||
|
Hello Stefi,
Thank you very much for your support. Indeed you served me with the answer for my original question, ffor which I am thankfull. When I checked the outcome, I realised I did not take this into account that unanswered questions may be caried over into the next month. That is why I posed my second question, which Roger answered. So again, thanks for your answer !! Kind regards, Jay -- ** Fool on the hill ** "Stefi" wrote: My solution is an answer exactly to what you asked. If I were you I would measure the elapsed time from date in to date closed: Then in sheet2 B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2)) 'This many questions were raised in the month C2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A3),--(Sheet1!C2:C9-Sheet1!B$2:B$9<=30)) 'From B2 this many questions were closed within 30 days Regards, Stefi "Jaydubs" wrote: Great thanks very much for this super answer. This is what I was looking for, but now I see that some of the questions are carried over to the next month........For instance a question is raised on the last day of the month and answered on the first day of the next month, how do I take this into consideration?? As these will not appear in the formula given by you ! -- ** Fool on the hill ** "Stefi" wrote: If on sheet1 you have these columns: A: question B: date in (format as date) C: date closed (format as date) and on sheet2 A: months (format as number) B: questions raised C: question closed in month of raising Then in sheet2 B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2)) C2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2),--(MONTH(Sheet1!C$2:C$9)=A2)) Regards, Stefi "Jaydubs" wrote: In a sheet in which I keep track of questions coming in, I not the date in and date closed. in another sheet I want to check how many questions were raised in a month and how many closed in the same month. Each question has its own line. How can I best tackle this? -- ** Fool on the hill ** |
|
#8
|
|||
|
|||
|
I also gave a modified answer to meet your new requirement!
Stefi "Jaydubs" wrote: Hello Stefi, Thank you very much for your support. Indeed you served me with the answer for my original question, ffor which I am thankfull. When I checked the outcome, I realised I did not take this into account that unanswered questions may be caried over into the next month. That is why I posed my second question, which Roger answered. So again, thanks for your answer !! Kind regards, Jay -- ** Fool on the hill ** "Stefi" wrote: My solution is an answer exactly to what you asked. If I were you I would measure the elapsed time from date in to date closed: Then in sheet2 B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2)) 'This many questions were raised in the month C2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A3),--(Sheet1!C2:C9-Sheet1!B$2:B$9<=30)) 'From B2 this many questions were closed within 30 days Regards, Stefi "Jaydubs" wrote: Great thanks very much for this super answer. This is what I was looking for, but now I see that some of the questions are carried over to the next month........For instance a question is raised on the last day of the month and answered on the first day of the next month, how do I take this into consideration?? As these will not appear in the formula given by you ! -- ** Fool on the hill ** "Stefi" wrote: If on sheet1 you have these columns: A: question B: date in (format as date) C: date closed (format as date) and on sheet2 A: months (format as number) B: questions raised C: question closed in month of raising Then in sheet2 B2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2)) C2: =SUMPRODUCT(--(MONTH(Sheet1!B$2:B$9)=A2),--(MONTH(Sheet1!C$2:C$9)=A2)) Regards, Stefi "Jaydubs" wrote: In a sheet in which I keep track of questions coming in, I not the date in and date closed. in another sheet I want to check how many questions were raised in a month and how many closed in the same month. Each question has its own line. How can I best tackle this? -- ** Fool on the hill ** |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| difficult calculation | Excel Worksheet Functions | |||
| Difficult to open files in Excel in different countries | Excel Discussion (Misc queries) | |||
| Very simple, but difficult formula question | Excel Worksheet Functions | |||
| A difficult thing¡¡¡¡¡¡ | Excel Discussion (Misc queries) | |||
| Difficult (for me) formula/UDF calculation | Excel Worksheet Functions | |||
Hybrid Mode
