Formula for If Term is on Certain Date then Count
 

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

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

Hi KJO

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


--
Paul Sheppard


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

Yea the pivot table does not quite work.

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

_WORK_SHEET_A_ [/b]

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

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

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

*PERSON/CANCELLED
Week 1
...
Week 8

_WORKSHEET_B_

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


Ok so what I need on Worksheet A is....If the type under -First Contact
Type - column on Worksheet B is a specific type on a specific date it
will show up as a count on Worksheet A. On Worksheet A the Rows titled
by week correspond to specific week. Week 1 is Aug. 15th-19th week 2 is
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 next
column titled -First Contact Type- the entry Phone what I would like to
see happen is then on Worksheet A under the subheading Phone on the row
titled week 3 and under the column titled # of Persons a 1 will
appear....So if I make similar entry on a different row on Worksheet B
with a specific date during week 3 and entered the type again as Phone
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 see
by week what contact types are happening and in what amount. Later on I
am going to use this information to see what contact types have the
highest results and on what weeks.

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

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