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

[Kikkoman]

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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kikkoman" <Kikkoman@discussions.microsoft.com> wrote in message
news:D5EC3BCA-85D3-40B3-805F-718F2E7DC97B@microsoft.com...
> 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
>

[Kikkoman]

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" <Kikkoman@discussions.microsoft.com> wrote in message
> news:D5EC3BCA-85D3-40B3-805F-718F2E7DC97B@microsoft.com...
> > 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
> >
>
>
>

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kikkoman" <Kikkoman@discussions.microsoft.com> wrote in message
news:22A5D9AF-68F4-4DA2-A1C0-3E4A2D8F5E0A@microsoft.com...
> Thx Max, it worked great.
> (Trying to work out where I went wrong!)