Count occurences based on dates

[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

 

[Sebation.G]

can u post a small sample

 

[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]

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_IN1>0))

Hope this can be helpful