if meet critertia then countif(a1:a10, between dates)
 

I hv a table of info

Col A = location
Col B = Due Dates

if meet location, then count the number of due dates that fall in Oct or nov
etc....

=SUMPRODUCT(--(A1:A10="location"),--(COUNTIF(b1:b10,DATEDIF("2005/10/1","2005/10/31","yd"))))

Col A Col B
Location Due Dates
USA 10/10/05
Canada 5/1/06
UK 31/12/05
Canada 2/1/06
S. America 30/10/05

Result: oct 05 nov 05 dec 05 jan 06
canada 0 0 0 2
S. America 2 0 0 0

Assuming source table is in A2:B10, and
this results part below is set-up in D1:H3
(E1:H1 contains "1st of month" dates,
viz.:1-Oct-2005, 1-Nov-2005 filled across)

Result: oct 05 nov 05 dec 05 jan 06
canada 0 0 0 2
S. America 1* 0 0 0
*corrected typo

Put in E2:

=SUMPRODUCT(($A$2:$A$10=$D2)*
($B$2:$B$10=DATE(YEAR(E$1),MONTH(E$1),1))*
(($B$2:$B$10<DATE(YEAR(E$1),MONTH(E$1)+1,1))))

Copy E2 across to H2, fill down to populate the table
and return the desired results

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kikkoman" wrote in message
...
I hv a table of info

Col A = location
Col B = Due Dates

if meet location, then count the number of due dates that fall in Oct or
nov
etc....


=SUMPRODUCT(--(A1:A10="location"),--(COUNTIF(b1:b10,DATEDIF("2005/10/1","200
5/10/31","yd"))))

Col A Col B
Location Due Dates
USA 10/10/05
Canada 5/1/06
UK 31/12/05
Canada 2/1/06
S. America 30/10/05

Result: oct 05 nov 05 dec 05 jan 06
canada 0 0 0 2
S. America 2 0 0 0

Thx Max, it worked great.
(Trying to work out where I went wrong!)



"Max" wrote:

Assuming source table is in A2:B10, and
this results part below is set-up in D1:H3
(E1:H1 contains "1st of month" dates,
viz.:1-Oct-2005, 1-Nov-2005 filled across)

Result: oct 05 nov 05 dec 05 jan 06
canada 0 0 0 2
S. America 1* 0 0 0
*corrected typo

Put in E2:

=SUMPRODUCT(($A$2:$A$10=$D2)*
($B$2:$B$10=DATE(YEAR(E$1),MONTH(E$1),1))*
(($B$2:$B$10<DATE(YEAR(E$1),MONTH(E$1)+1,1))))

Copy E2 across to H2, fill down to populate the table
and return the desired results

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kikkoman" wrote in message
...
I hv a table of info

Col A = location
Col B = Due Dates

if meet location, then count the number of due dates that fall in Oct or
nov
etc....


=SUMPRODUCT(--(A1:A10="location"),--(COUNTIF(b1:b10,DATEDIF("2005/10/1","200
5/10/31","yd"))))

Col A Col B
Location Due Dates
USA 10/10/05
Canada 5/1/06
UK 31/12/05
Canada 2/1/06
S. America 30/10/05

Result: oct 05 nov 05 dec 05 jan 06
canada 0 0 0 2
S. America 2 0 0 0

Glad it helped !

... and just realized there was an extra, unnecessary pair of parens (around
the 3rd cond) in the earlier formula, sorry.

Put instead in E2, and fill across and down:

=SUMPRODUCT(($A$2:$A$10=$D2)*
($B$2:$B$10=DATE(YEAR(E$1),MONTH(E$1),1))*
($B$2:$B$10<DATE(YEAR(E$1),MONTH(E$1)+1,1)))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kikkoman" wrote in message
...
Thx Max, it worked great.
(Trying to work out where I went wrong!)