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,"<=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.
I have uploaded the sheet to a website if it would help you in assisting me.
Thank you.
Posted by Mark W. on July 24, 2001 8:17 AM
{=SUM((A2:A91<="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
operators to suit your needs.
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<=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.
Aladin
That wouldn't be nice
Posted by Deirdre on July 24, 2001 8:30 AM
Oh Bless you! Thank you Thank you!
:: runs off to store to buy a wig to cover up self-induced bald spot ::
