Formulas and worksheet linking

[amandaa]

Hi Please can some one help as I am having problems!! I have a workbook with two worksheets on the first is the following:

A B

1 Name Date Called

2 John Smith 06/09/2005

3 Fred Bloggs 07/05/2005

4 Bart Simpson 12/09/2005

5 Bugs Bunny 23/07/2005

This is an on going worksheet which will be added to constantly with new clients and dates called.


In the second worksheet is the following:


A B

1 Month Calls Made

2 January

3 February

4 March

5 April etc.....


My question, is how do I create a fomula in order to complete collumn B in worksheet 2 with the total number of calls made during that month to any client? I think I need to use the COUNTIF function, however I do not know how to use with date ranges.

If someone could help that would be great!
Thanks
Amanda

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>

[Duke Carey]

If all your dates are from the same year use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#))

month# is 1 for Jan, 2 for Feb, etc

If you have to worry about the year, too, then use

=SUMPRODUCT(--(MONTH(Sheet1!B2:B500)=month#),--(YEAR(Sheet1!B2:B500)=2005))


"amandaa" wrote:

>
> Hi Please can some one help as I am having problems!! I have a workbook
> with two worksheets on the first is the following:
>
> A B
>
> 1 Name Date Called
>
> 2 John Smith 06/09/2005
>
> 3 Fred Bloggs 07/05/2005
>
> 4 Bart Simpson 12/09/2005
>
> 5 Bugs Bunny 23/07/2005
>
> This is an on going worksheet which will be added to constantly with
> new clients and dates called.
>
>
> In the second worksheet is the following:
>
>
> A B
>
> 1 Month Calls Made
>
> 2 January
>
> 3 February
>
> 4 March
>
> 5 April etc.....
>
>
> My question, is how do I create a fomula in order to complete collumn B
> in worksheet 2 with the total number of calls made during that month to
> any client? I think I need to use the COUNTIF function, however I do
> not know how to use with date ranges.
>
> If someone could help that would be great!
> Thanks
> Amanda
>
>
> --
> amandaa
> ------------------------------------------------------------------------
> amandaa's Profile: http://www.excelforum.com/member.php...o&userid=27010
> View this thread: http://www.excelforum.com/showthread...hreadid=420962
>
>