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

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
.................................... # 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 ....................................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... .

KJO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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