Formula Help

Occasional Contributor

Hi, i need help with a formula.  I have a file that in one column has codes  the other column has total of time  (basically it's a time sheet for an employee and they code what they did in that particular time) So i want to return if equals T for travel then add up all the hours in that day they traveled.  Is this possible?

26 Replies


For example in H29:


=SUMIF(E6:E27,"T ",C6:C27)


Note that I used "T ". This is because the code in Y12 contains a space after T. If you remove this, use



Thank you.  ! more questions seems my hours are rounding up can you check my ttotals  how do i fix that@Hans Vogelaar 


I'm sorry, I don't understand. Can you provide an example to explain what you mean? Thanks in advance.

so different question, i need to get total hours of billable  column e, if "AI", "W" and "CDAM" then add hours in column C  it's not working my formula@Hans Vogelaar 


Do you mean




Not sure about formulas, but time is entered incorrectly, for example here


is not 12AM, but 12AM plus 6 days.

If apply elapsed time format, Time Out will be 144:00


Perhaps you need to clean source data first.

tHANK YOU. Our mechanic should've tuped 6:00 am

Thank you. One more I now need to add up how many AI, W, CDAM and B in cell below that cell


Perhaps they typed just 6 and the result was 6th day from the beginning of Excel dates. It's better to add data validation rule which allows to enter the time only between 0:00 and 23:59:59 as


I jumped in only with this topic, I believe @Hans Vogelaar will help with the rest

This is a great idea. Question, i did one cell and tried to copy formula and it wouldn't.

How do i get them all there are 2 columns and 365 lines thank you for helping me
This is great, so i did it in one cell but how do i get all 2 columns and 365 lines of time entry to be the same?
I have 2 codes now "TN" and "TB" if I add them it doesn't work
@Hans Vogelaar I have 2 codes now "TN" and "TB" if I add them it doesn't work I NEED TO COUNT HOW MANY OF MORE THAN ONE CODE IN A SPECIFIC CELL



> I now need to add up how many AI, W, CDAM and B in cell below that cell 




What is wrong with



@Hans Vogelaar becasue i want to know how many TB's, and how many TN's and how many T's, and How many NB's.  so it's a countif but its not working.  

Under travel i want to know not only how may hours of TB and TN and T but HOw many TB and TN and T   maybe it's 5???


You may select all needed cells and apply data validation rule to all of them at once. Or apply the rule to one cell/range and copy/paste this cell/range to other locations, dfata validation will be applied with that.


You can use






@Hans Vogelaar i saved the file in a template and now cant open