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

Counting with Excel
 

=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" wrote in message
...
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

Counting with Excel
 

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


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=498253

Counting with Excel
 

Ooops...misread your post:

=SUMPRODUCT((A1:A5="B OP")*(C1:C5=D1)*( C1:C5<=D2))

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=498253

Counting with Excel
 

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" wrote in message
...
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