ExcelBanter - Countif between Time stamps and have

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Countif between Time stamps and have - value (https://www.excelbanter.com/excel-worksheet-functions/445698-countif-between-time-stamps-have-value.html)

Countif between Time stamps and have - value

Hi

Really need help for this problem. I have data in time stamp (plus the data also have - value), need to count data that greater than 8:30:00 AM and to minus the "- value". Below are my data

*Date: * log in:
*Sun 1/1/2012 * -
*Mon 2/1/2012 * -
*Tue 3/1/2012 * 8:27:59 AM
*Wed 4/1/2012 * 8:33:01 AM
*Thu 5/1/2012 * 8:06:05 PM
*Fri 6/1/2012 * 8:24:36 AM
*Sat 7/1/2012 * -
*Sun 8/1/2012 * -
*Mon 9/1/2012 * 8:30:47 AM
*Tue 10/1/2012 * -
*Wed 11/1/2012 * 1:40:30 PM
*Thu 12/1/2012 * -

Have try several formula:
1. =COUNTIF(B5:B35," 8:30:00") will give answer 31

2. =SUMPRODUCT((B7:B10,B13,B15,B17,B20:B24,B30:B31,B3 4:B35))=M5 where i exclude the "-value" -- will give error value

3. =SUMPRODUCT(--(HOUR(B5:B35)=M5*24)) also will give error value

Thanks

Quote:

Originally Posted by akmazaini (Post 1600536)

Hi,

Not 100% sure I understand what you mean. Could you post your workbook so we can have a better look?