Oct 08 2020 08:46 AM
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?
Oct 08 2020 08:54 AM
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)
Oct 08 2020 09:50 AM
Thank you. ! more questions seems my hours are rounding up can you check my ttotals how do i fix that@Hans Vogelaar
Oct 08 2020 10:03 AM
I'm sorry, I don't understand. Can you provide an example to explain what you mean? Thanks in advance.
Oct 08 2020 01:25 PM
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
Oct 08 2020 01:35 PM
Oct 08 2020 01:42 PM
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.
Oct 09 2020 08:34 AM
Oct 09 2020 09:41 AM
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
Oct 09 2020 10:06 AM
Oct 09 2020 10:16 AM
Oct 09 2020 10:16 AM
Oct 09 2020 10:39 AM
Oct 09 2020 12:10 PM
> 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))
Oct 09 2020 12:11 PM
Oct 09 2020 02:30 PM
@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???
Oct 09 2020 03:50 PM
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.
Oct 10 2020 03:15 AM
You can use
=COUNTIF(E3:E27,"T")
=COUNTIF(E3:E27,"TN")
=COUNTIF(E3:E27,"TB")
=COUNTIF(E3:E27,"NB")
Oct 12 2020 07:50 AM
@Hans Vogelaar i saved the file in a template and now cant open