How does one gets the total sorted job time?

D

_dave

New member
Joined
May 11, 2016
Messages
21
Reaction score
0
Points
0
Excel Version(s)
2013
I am lost on trying to total the time for all jobs based on the job ID.
Column A,B,C are the columns for the date, job ID and time performing the job.
Column E and F are the the job ID and the total time spent on each job.

I cannot figure out how to perform the functions for column E and F.

Any suggestion on how to do this would be great. Thanks in advance.

Included is a sample of the spreadsheet

_dave
 

Attachments

  • Time Log.xlsx
    9.6 KB · Views: 10
Try
Code: 
=SUMIF($B$2:$C$9,E2,$C$2:$C$9)
 
Hi navic, column E and F are my results. I to find all jobs and list only once in column E, while getting the sum of each jobs in column F.
So if I have worked on job number 1001 3 times I need a column (E) that list that job only once and give me the sum total of the time worked in column (F).

I hope I am a little clearer on what I am doing.

Thanks for helping.

_dave
 
I do not understand you, sorry.
Can you attach respective sheet, with the expected results.
I want to see the sheet before and after.

If you're looking for unique data in column E and also if you need formula, try ARRAY formula bellow in E2 cell
Code: 
=IFERROR(SMALL(IF(FREQUENCY($B$2:$B$9,$B$2:$B$9),$B$2:$B$9),ROWS($1:1)),"")
in the F column put formula as I wrote
Code: 
=SUMIF($B$2:$C$9,E2,$C$2:$C$9)
Does this solution helps?
 
Last edited:
navic said:
I do not understand you, sorry.
Can you attach respective sheet, with the expected results.
I want to see the sheet before and after.

If you're looking for unique data in column E and also if you need formula, try ARRAY formula bellow in E2 cell
Code: 
=IFERROR(SMALL(IF(FREQUENCY($B$2:$B$9,$B$2:$B$9),$B$2:$B$9),ROWS($1:1)),"")
in the F column put formula as I wrote
Code: 
=SUMIF($B$2:$C$9,E2,$C$2:$C$9)
Does this solution helps?
Click to expand...

Yes, it does help in what I want. How will it "see" the other "job ID's" as well?
The worksheet is both a before "date", "job ID", and "time" would be "before" while "after" would be "all job ID and TOTAL TIME".

I added Time Log v2 with your suggested way of moving forward.

I am afraid that as the job ID numbers are added (every one will be a different number) your functions may need to grow as well or am I missing something (I'm sure I am).

Again, thanks for the help.

_dave
 

Attachments

  • Time Log v2.xlsx
    9.6 KB · Views: 8
Create unique values from one column

_dave said:
I am afraid that as the job ID numbers are added (every one will be a different number) your functions may need to grow as well or am I missing something (I'm sure I am).
Click to expand...
I wrote you
try ARRAY formula bellow in E2 cell
Click to expand...
So, Unique formula need to be finished with CTRL+SHIFT+ENTER (not just enter). Then copy down
See attach
 

Attachments

  • Time Log v2.xlsx
    9.8 KB · Views: 13
@dave Please do not quote entire posts unnecessarily; They clutter the thread and make it hard to read. Thank you
 
Declare your data list as a table and use that table as source for a Pivot Table. No fancy and slowing down formulas necessary
 
Thank you for telling me, still learning here....
 
I do not know how to do that (create table and Pivot Table). Could you give me a pointer or a link on how to?
 
I see that now, now when I add row 10 it does not update.
Do I need to change "$B2:$B$9" to something like "$B2:$B:$B" to get each added job?
 
[Solved]
To both "navic" and "pecoflyer" -
Thanks guys, by learning and using the "table" format it is just what I needed for my time log.
 
You must log in or register to reply here.
Back
Top