What are a few unique Chinese values?

How do i sum unique values

• I am trying to sum unique values in a spreadsheet that tracks tutoring hours. Example: Column B = start time (in hrs.). Column C = end time (in hrs). Column D = total time. Each row is an individual student. There are some students that have the same start time & end time (ex. 2 different students [students X & Y]start at 1pm and end at 2pm) I need to be able to sum the total number of hours that the tutor tutored students (in essence a timesheet for the tutor). If I were to merely sum Column D I would get MORE hours than actually tutored (ex. Students X & Y would add up to 2 hours, when in actuality only 1 hour of time lapsed). How would I sum Column D so that only UNIQUE values (i.e. unique times) in Columns B&C are included (and therefore identical tutoring times are excluded)? Sincerely, Nate

• Answer:

  Hello Nate: Name Start End Total Hrs Student 1 8:30 AM 10:30 AM 2:00 =sum(c9-b9) Student 2 10:30 AM 1:00 PM 2:30 Student 3 1:00 PM 2:00 PM 1:00 Student 4 2:30 PM 3:40 PM 1:10 Student 5 4:00 PM 5:30 PM 1:30 same start time Student 6 4:00 PM 7:30 PM 3:30 Student 7 6:30 PM 7:30 PM 1:00 Student 8 5:00 PM 7:00 PM 2:00 Student 9 6:15 PM 7:45 PM 1:30 Student 10 5:35 PM 8:00 PM 2:25 Please use the formula =sum(c9-b9) which translates to subtracting the end time from the start time. Hope this helps. Hello Nate, Let me suggest that you send me an email ([email protected]) with a sampleproblem and I will provide you with a timesheet example that shows the formula used. Look forward to assisting you with this. Have a great evening. Mary Hi Nate: Try the following: Lets say that your start times are in E1:E7 and your finish times are in F1:F7, the formula you want to calculate the total time is =SUM(IF(MATCH(E1:E7,$E$1:$E$7,0)=ROW(E1:E7)-ROW($E$1)+1,HOUR(F1:F7)-HOUR(E1:E7),0)) Just substitute in the columns and rows as necessary. You will need to enter this as an array formula. This means that instead of hitting enter, you hit control/shift/Enter after typing or pasting in the formula. You will know if you have done that properly if {} curly brackets appear round the formula. I also suggest that you use dynamic named ranges if you want this to be generalized. I'm assuming that your times have been entered asTimes. If not then you may need to fiddle with the Hour() calculations. See these links for background on array formulas and here for named ranges.