Formula Help

Copper 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

@MelissaChristensen 

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)

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

@MelissaChristensen 

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 

@MelissaChristensen 

Do you mean

 

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

@MelissaChristensen 

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

image.png

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

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

image.png

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

@MelissaChristensen 

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

image.png

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

@MelissaChristensen 

 

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

@MelissaChristensen 

What is wrong with

 

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

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

@MelissaChristensen 

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.

@MelissaChristensen 

You can use

 

=COUNTIF(E3:E27,"T")

=COUNTIF(E3:E27,"TN")

=COUNTIF(E3:E27,"TB")

=COUNTIF(E3:E27,"NB")

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