You are here:

# Excel/Countifs with time values

Question
Hi Nathan

On Sheet 1, I have a table with 3 columns, 'start', 'finish', 'room'.  Start and Finish are formatted as time eg.  8:00:00 AM

On Sheet 2, I have times in 10 min increments (formatted to time as above) in column A.  I want to count the number of times a room is used between the time eg 8.10am and 8.20am.

My formula which is not working - reads =Countifs(Sheet1!\$c\$2:\$c\$1000, "=Room1", Sheet1!\$a\$2:\$a\$1000, ">="&\$a2, Sheet1!\$c\$2:\$c\$1000, "<"&\$a3)

Am I using the correct formula type?

Mireille:

You are very close.  Here are some initial comments:

1) You don't need the = sign before Room1.  It can just be "Room1"
2) Your third element is looking at column C for the end date... it should be looking at column B.

Once you make those changes the formula will work; however, it only works if the start and end time fall within exactly a 10 minute increment.  In other words, if the time the room is in use is from 9:05 AM to 9:15 AM then it will show zero.  I anticipate you would actually prefer to show it in use during both applicable 10 minute periods.  As such, if that is the case, you would want to use this formula:

=COUNTIFS(Sheet1!\$C\$2:\$C\$1000,"Room1", Sheet1!\$A\$2:\$A\$1000,"<="&\$B2, Sheet1!\$B\$2:\$B\$1000,">"&\$A2)

All I did was switch criteria around a little to get the proper selection.

I have uploaded my example here:

https://www.dropbox.com/s/uerw2tk88vx1tp3/ForMireille.xlsx

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Nathan

##### Expertise

This expert requires a donation of \$5 for questions answered (satisfaction guaranteed). Isn't \$5 a value for this experts time?
Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (EXCEPT Visual Basic Coding/Programming and Macros, I don't answer questions in those categories).

##### Experience

I have been using spreadsheets since Lotus 1-2-3 was released.

Education/Credentials
Certified Public Accountant (CPA)