Difficult but do-able?

Jaydubs · 10-03-2005, 06:05 AM

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 **

Stefi · 10-03-2005, 07:05 AM

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 **

Jaydubs · 10-04-2005, 02:05 AM

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 **

Roger Govier · 10-04-2005, 06:05 AM

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 !
>
>

Stefi · 10-04-2005, 06:05 AM

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 **

Jaydubs · 10-04-2005, 06:05 AM

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 !
> >
> >
>

Jaydubs · 10-04-2005, 06:05 AM

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 **

Stefi · 10-06-2005, 05:05 AM

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 **

Jaydubs · 10-06-2005, 06:05 AM

Yes indeed you did, thanks !!
--
** Fool on the hill **

"Stefi" wrote:

> 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 **