Formulas and worksheet linking

[amandaa]

Hi Please can some one help as I am having problems!! I have a workboo
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 wit
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
in worksheet 2 with the total number of calls made during that month t
any client? I think I need to use the COUNTIF function, however I d
not know how to use with date ranges.

If someone could help that would be great!
Thanks
Amand

 

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