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

[Guest]

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

 

[Max]

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

--

 

[Guest]

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

 

[Max]

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