software

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 amp; 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

=SUMPRODUCT(--(A1:A5=quot;Bquot;),--(B1:B5gt;=DATE(2005,4,1)),--(B1:B5lt;=DATE(2005,4,17
)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

Try:

=SUMPRODUCT((A1:A5=quot;Bquot;)*(B1:B5=quot;opquot;)*(C1:C5gt;=D1)*( C1:C5lt;=D2))

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

HTH
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=498253
Ooops...misread your post:

=SUMPRODUCT((A1:A5=quot;B OPquot;)*(C1:C5gt;=D1)*( C1:C5lt;=D2))

Regards
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=498253Oh Excellent! Thank you very much!

quot;Bob Phillipsquot; wrote:

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