 Highlighted

# Formula Help

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
Highlighted

# Re: Formula Help

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

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

Highlighted

# Re: Formula Help

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

Highlighted

# Re: Formula Help

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

Highlighted

# Re: Formula Help

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

Highlighted

# Re: Formula Help

Do you mean

=SUM(SUMIF(E3:E27,{"AI","W","CDAM"},C3:C26))

Highlighted

# Re: Formula Help

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.

Highlighted

# Re: Formula Help

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

Highlighted

# Re: Formula Help

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

# Re: Formula Help

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

Highlighted

# Re: Formula Help

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
Highlighted

# Re: Formula Help

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?
Highlighted

# Re: Formula Help

I have 2 codes now "TN" and "TB" if I add them it doesn't work
Highlighted

# Re: Formula Help

@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
Highlighted

# Re: Formula Help

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

=SUM(SUMIF(E3:E27,{"AI","W","CDAM","B"},C3:C26))

Highlighted

# Re: Formula Help

What is wrong with

=SUM(SUMIF(E3:E27,{"TN","TB"},C3:C26))

Highlighted

# Re: Formula Help

@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???

Highlighted

# Re: Formula Help

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.

Highlighted

# Re: Formula Help

You can use

=COUNTIF(E3:E27,"T")

=COUNTIF(E3:E27,"TN")

=COUNTIF(E3:E27,"TB")

=COUNTIF(E3:E27,"NB")

Highlighted

# cant open file

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