Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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... :) |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value between 2 dates | Excel Worksheet Functions | |||
Date Formula | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel | |||
excel formula counting date to date in 4 columns | Excel Worksheet Functions | |||
date format within a cell containing a formula | Excel Discussion (Misc queries) |