# COUNTIF or not...

Posted by Deirdre on July 24, 2001 7:57 AM

:: pulls out hair ::

I have a colum of times in military format. These times correspond to a product being produced. If a time is enterd, that means a product has been produced.

I wish to count the number of products produced in an hour. I've tried setting up the function =COUNTIF(A2:A91,"&LT;=8:00") which of course does count the Number of instances below and equal to 8:00.

HOWEVER I cannot figure out how to set this up to count the number of instances BETWEEN 7:00 and 8:00. Whenever I try, it returns a value of 0 even though there is faux date entered which would return a number other than 0.

Thank you.

Posted by Mark W. on July 24, 2001 8:17 AM

{=SUM((A2:A91&LT;="8:00"+0)*(A2:A91>="7:00"+0))}

This is an array formula which must be entered
using Control+Shift+Enter. The braces, {}, are
not entered by you. You may modify the relational

Posted by Aladin Akyurek on July 24, 2001 8:29 AM

Deirdre,

I've seen the worksheet you refer to.

I'd suggest using column D and E, leaving C empty (would look nicer that way.

In D1 enter: 7:00
In E1 enter: 8:00
In D2 enter: 8:00 (no need for 8:01!)
In E2 enter: 9:00
and so on.

In F1 enter: =SUMPRODUCT((\$A\$2:\$A\$91>D2)*(\$A\$2:\$A\$91&LT;=E2)*(\$B\$2:\$B\$91))

where A2:A91 contains time points and B2:B91 the qty produced at those time points.

Copy down this formula as far as needed.