MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 ::