Counting with Excel

I want to count the number of times a name appears in one column, if the date
in another column falls in a certain period. For instance: Count If A1:A5=B
OP and the date in B1:B5 is between 01/04/05 & 17/04/05.
ColA ColB
B OP 15/04/05
B LD 15/04/05
B OP 13/04/05
B OP 18/04/05
B PD 13/04/05 with the answer being 2!

The way the sheet is set up will not work in a pivot table (I don't think)
and i don't want to alter it. I've tried someproduct but can't get it to
work, i'm either writing it wrong or using the wrong formula. PLEASE HELP!!!!!

Thanks

Becks

Try:

=SUMPRODUCT((A1:A5="B")*(B1:B5="op")*(C1:C5>=D1)*(C1:C5<=D2))

where D1 is 01/04/05
and D2 is 17/04/05

HTH
JG

=SUMPRODUCT(--(A1:A5="B"),--(B1:B5>=DATE(2005,4,1)),--(B1:B5<=DATE(2005,4,17
)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Becks" <Becks@discussions.microsoft.com> wrote in message
news:E6DD635D-0972-4FB0-A664-0D3C1A9F7FB1@microsoft.com...
> I want to count the number of times a name appears in one column, if the
date
> in another column falls in a certain period. For instance: Count If
A1:A5=B
> OP and the date in B1:B5 is between 01/04/05 & 17/04/05.
> ColA ColB
> B OP 15/04/05
> B LD 15/04/05
> B OP 13/04/05
> B OP 18/04/05
> B PD 13/04/05 with the answer being 2!
>
> The way the sheet is set up will not work in a pivot table (I don't think)
> and i don't want to alter it. I've tried someproduct but can't get it to
> work, i'm either writing it wrong or using the wrong formula. PLEASE
HELP!!!!!
>
> Thanks
>
> Becks

Ooops...misread your post:

=SUMPRODUCT((A1:A5="B OP")*(C1:C5>=D1)*( C1:C5<=D2))
D1 - 01/04/05
D2 - 17/04/05

Regards
JG

Oh Excellent! Thank you very much!

"Bob Phillips" wrote:

> =SUMPRODUCT(--(A1:A5="B"),--(B1:B5>=DATE(2005,4,1)),--(B1:B5<=DATE(2005,4,17
> )))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Becks" <Becks@discussions.microsoft.com> wrote in message
> news:E6DD635D-0972-4FB0-A664-0D3C1A9F7FB1@microsoft.com...
> > I want to count the number of times a name appears in one column, if the
> date
> > in another column falls in a certain period. For instance: Count If
> A1:A5=B
> > OP and the date in B1:B5 is between 01/04/05 & 17/04/05.
> > ColA ColB
> > B OP 15/04/05
> > B LD 15/04/05
> > B OP 13/04/05
> > B OP 18/04/05
> > B PD 13/04/05 with the answer being 2!
> >
> > The way the sheet is set up will not work in a pivot table (I don't think)
> > and i don't want to alter it. I've tried someproduct but can't get it to
> > work, i'm either writing it wrong or using the wrong formula. PLEASE
> HELP!!!!!
> >
> > Thanks
> >
> > Becks
>
>
>