Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1759772%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1759772%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20i%20need%20help%20with%20a%20formula.%26nbsp%3B%20I%20have%20a%20file%20that%20in%20one%20column%20has%20codes%26nbsp%3B%20the%20other%20column%20has%20total%20of%20time%26nbsp%3B%20(basically%20it's%20a%20time%20sheet%20for%20an%20employee%20and%20they%20code%20what%20they%20did%20in%20that%20particular%20time)%20So%20i%20want%20to%20return%20if%20equals%20T%20for%20travel%20then%20add%20up%20all%20the%20hours%20in%20that%20day%20they%20traveled.%26nbsp%3B%20Is%20this%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1759772%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1760555%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1760555%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you.%26nbsp%3B%20!%20more%20questions%20seems%20my%20hours%20are%20rounding%20up%20can%20you%20check%20my%20ttotals%26nbsp%3B%20how%20do%20i%20fix%20that%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1760624%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1760624%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F824928%22%20target%3D%22_blank%22%3E%40MelissaChristensen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20sorry%2C%20I%20don't%20understand.%20Can%20you%20provide%20an%20example%20to%20explain%20what%20you%20mean%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1759890%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1759890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F824928%22%20target%3D%22_blank%22%3E%40MelissaChristensen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%20in%20H29%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIF(E6%3AE27%2C%22T%20%22%2CC6%3AC27)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%20that%20I%20used%20%22T%20%22.%20This%20is%20because%20the%20code%20in%20Y12%20contains%20a%20space%20after%20T.%20If%20you%20remove%20this%2C%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIF(E6%3AE27%2C%22T%22%2CC6%3AC27)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

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

Highlighted

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

Highlighted

@MelissaChristensen 

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

Highlighted

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

@MelissaChristensen 

Do you mean

 

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

Highlighted

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

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

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

@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

Highlighted
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
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
I have 2 codes now "TN" and "TB" if I add them it doesn't work
Highlighted
@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

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

Highlighted

@MelissaChristensen 

What is wrong with

 

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

Highlighted

@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

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

Highlighted

@MelissaChristensen 

You can use

 

=COUNTIF(E3:E27,"T")

=COUNTIF(E3:E27,"TN")

=COUNTIF(E3:E27,"TB")

=COUNTIF(E3:E27,"NB")

Highlighted

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