Formula for If Term is on Certain Date then Count

[Krisjhn]

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

 

[Paul Sheppard]

I am trying to figure out a formula for a spreadsheet I am creating.
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 Workshee
A
2] Info on Worksheet A I am trying to get is if sometheing happens on
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 Dat
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 b
type and date.
8] If I were to give someone isntructions I would say Add up all th
Phone Types during the week of Aug 15-19th and put then in row 1. D
the same for the two other contact types. Continue to do this for th
next 8 weeks.

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

Anyhelp you can give would be appreciated

Thanks

KJO

Hi KJO

Not sure if this helps, but a pivot table would give you exactly wha
you want

 

[Krisjhn]

Yea the pivot table does not quite work.

Maybe if I give an example of what my spreadsheet looks like that wil
help

_WORK_SHEET_A_ [/B]

.................................... *# OF PERSONS*

*PHON
Week 1
week 2
week 3
week 4
week 5
week 6
week 7
week 8

PERSON/TOUR *
WEEK 1
...
...
WEEK 8

*PERSON/CANCELLE
Week 1
...
Week 8

_WORKSHEET_B

FIRST CONTACT DATE * ....................................FIRS
CONTACT TYP
[end user must enter specific date] ..............[end user must ente
1 of 3 types: Phone, Person/Tour, Person/Enrolled]


Ok so what I need on Worksheet A is....If the type under -First Contac
Type - column on Worksheet B is a specific type on a specific date i
will show up as a count on Worksheet A. On Worksheet A the Rows title
by week correspond to specific week. Week 1 is Aug. 15th-19th week 2 i
Aug 22-26th .... onto week 8 which is Oct 3-7th.

So for example On WorkSheet B if I input into -First Contact Date
Column the dat of Sept 1 and then Enter on the same row but the nex
column titled -First Contact Type- the entry Phone what I would like t
see happen is then on Worksheet A under the subheading Phone on the ro
titled week 3 and under the column titled # of Persons a 1 wil
appear....So if I make similar entry on a different row on Worksheet
with a specific date during week 3 and entered the type again as Phon
the # of Persons count on Worksheet A would increase to two.

So what I need is a formula or a way to make this happen so I can se
by week what contact types are happening and in what amount. Later on
am going to use this information to see what contact types have th
highest results and on what weeks.

Thanks for the help. I hope this is not confusing. I fear I will neve
become a technical writer...may parents are going to be crushed... :

 

[B. R.Ramachandran]

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