Hi,

Let's say, the First Contact Date and First Contact Type are contained in
Columns A and B repectively in Sheet B, starting at Row 2.
In Sheet A:
First create a column (say A) to contain the beginning date of each week in
rows starting with row 2 (eg., A2=08/15/05, A3=08/22/05, €¦.).
Next, create headers for the three contact types (Phones, Person/Tour,
Person/Enrolled) in B1, C1, and D1, respectively.
In B2 enter the formula, exactly as shown (the $ signs and the parentheses
are important).

=SUMPRODUCT(--(B!$A$2:$A$1000=A!$A2),--(B!$A$2:$A$1000<$A2+5),--(B!$B$2:$B$1000=A!B$1))

You can change the '1000' in the formula to any other large number to
accommodate all the rows of Sheet B.

Drag the formula into C2 and D2, and then the drag it down to the last row
(of columns B, C, and D).

This formula will ignore data if the end-user inputs a weekend date in Sheet
B. If you want to allow weekend dates too, change the '5' in the formula to
'7')

Regards,

B. R. Ramachandran



"Krisjhn" wrote:


I am trying to figure out a formula for a spreadsheet I am creating. I
am stumped. Any help you can give me would be appreciated.

So here is what I am trying to do.

1] Trying count some information on Worksheet B and Put in on Worksheet
A
2] Info on Worksheet A I am trying to get is if sometheing happens on a
certain date then it is counted on Worksheet B and put on worksheet A
3] On Worksheet B I have two Columns one is titled First Contact Date
and the second is First Contact Type
4] In First Contact Date end user has to enter a specific date
5] In First Contact Type end User has to enter 1 of three terms: Phone,
Person/Tour, Person/Enrolled
6] What info I need on Worksheet A is a count of terms seperated by
type and date.
8] If I were to give someone isntructions I would say Add up all the
Phone Types during the week of Aug 15-19th and put then in row 1. Do
the same for the two other contact types. Continue to do this for the
next 8 weeks.

So that is what I need a way to say if the date is between these dates
in column 1 and they type is this specific type in column b then put it
into this spot on worksheet a.

Anyhelp you can give would be appreciated

Thanks

KJO


--
Krisjhn
------------------------------------------------------------------------
Krisjhn's Profile: http://www.excelforum.com/member.php...o&userid=26796
View this thread: http://www.excelforum.com/showthread...hreadid=400502