Count occurences based on dates
 

Hi,
Hope someone can help me.
Column A contains dates an item was first sent out.
Column B contains dates the item was first returned.
Column C contains the status of the item (A,B or C).
Column D contains dates an item was sent out for a second time.
Column E contains dates the item was returned for the second time.
Column F contains the status of the item (A,B or C) after the second return.
With me so far?

I need a formula which will count the number of cells in column C which are
A, B or C but ONLY if the date in Column B is within the last 7 days.
However, if there is a value in Column F, I need it to ignore column C and
count that value instead.

I hoope the above is clear and someone can help me out. If it is not clear
maybe I can post a small sample of what I require.

Many thanks

Count occurences based on dates
 

can u post a small sample
"Laurence" ...
Hi,
Hope someone can help me.
Column A contains dates an item was first sent out.
Column B contains dates the item was first returned.
Column C contains the status of the item (A,B or C).
Column D contains dates an item was sent out for a second time.
Column E contains dates the item was returned for the second time.
Column F contains the status of the item (A,B or C) after the second
return.
With me so far?

I need a formula which will count the number of cells in column C which
are
A, B or C but ONLY if the date in Column B is within the last 7 days.
However, if there is a value in Column F, I need it to ignore column C and
count that value instead.

I hoope the above is clear and someone can help me out. If it is not clear
maybe I can post a small sample of what I require.

Many thanks

Count occurences based on dates
 

Sebation

Thanks for your response. Hope this helps:


Report Run Date 1 August 07

Submission 1 Submission 2
Date Out Date In Status Date Out
Date In Status
02/06/2007 16/06/2007 A
05/06/2007 28/07/2007 A
17/06/2007 29/06/2007 C 21/07/2007 27/07/2007 A
15/05/2007 15/06/2007 B 30/06/2007 26/07/2007 A
16/05/2007 16/06/2007 B 19/06/2007 29/07/2007 C
18/06/2007 23/06/2007 A

Total Submissions In For Last 7 Days 4
Total Submissions In Status A Last 7 Days 3
Total Submissions In Status B Last 7 Days 0
Total Submissions In Status C Last 7 Days 1

Can you advise whether the formula can update the results every time I open
the Worksheet.


"Sebation.G" wrote:

can u post a small sample
"Laurence" ...
Hi,
Hope someone can help me.
Column A contains dates an item was first sent out.
Column B contains dates the item was first returned.
Column C contains the status of the item (A,B or C).
Column D contains dates an item was sent out for a second time.
Column E contains dates the item was returned for the second time.
Column F contains the status of the item (A,B or C) after the second
return.
With me so far?

I need a formula which will count the number of cells in column C which
are
A, B or C but ONLY if the date in Column B is within the last 7 days.
However, if there is a value in Column F, I need it to ignore column C and
count that value instead.

I hoope the above is clear and someone can help me out. If it is not clear
maybe I can post a small sample of what I require.

Many thanks

Count occurences based on dates
 

count A u can use
=SUMPRODUCT((final
date-DATA_IN1<=7)*(C1:C6="A")*(DATA_IN2-DATAIN1<0))+SUMPRODUCT(--(final
date-DATA_IN2<=7)*(F1:F6="A")*(DATA_IN2-DATA_IN10))

Hope this can be helpful

--
Regards,

Sebation.G

"Laurence" ...
Sebation

Thanks for your response. Hope this helps:


Report Run Date 1 August 07

Submission 1 Submission 2
Date Out Date In Status Date Out
Date In Status
02/06/2007 16/06/2007 A
05/06/2007 28/07/2007 A
17/06/2007 29/06/2007 C 21/07/2007 27/07/2007 A
15/05/2007 15/06/2007 B 30/06/2007 26/07/2007 A
16/05/2007 16/06/2007 B 19/06/2007 29/07/2007 C
18/06/2007 23/06/2007 A

Total Submissions In For Last 7 Days 4
Total Submissions In Status A Last 7 Days 3
Total Submissions In Status B Last 7 Days 0
Total Submissions In Status C Last 7 Days 1

Can you advise whether the formula can update the results every time I
open
the Worksheet.


"Sebation.G" wrote:

can u post a small sample
"Laurence" ...
Hi,
Hope someone can help me.
Column A contains dates an item was first sent out.
Column B contains dates the item was first returned.
Column C contains the status of the item (A,B or C).
Column D contains dates an item was sent out for a second time.
Column E contains dates the item was returned for the second time.
Column F contains the status of the item (A,B or C) after the second
return.
With me so far?

I need a formula which will count the number of cells in column C which
are
A, B or C but ONLY if the date in Column B is within the last 7 days.
However, if there is a value in Column F, I need it to ignore column C
and
count that value instead.

I hoope the above is clear and someone can help me out. If it is not
clear
maybe I can post a small sample of what I require.

Many thanks