We help IT Professionals succeed at work.

Accumulating a total in one call against a particular value in another cell.

23 Views
Last Modified: 2020-04-26
I have a spreadsheet (attached) - for timesheets - in which a job number (entered in Column C) is allocated a time expended on it by the operative (start and finish times in columns F and G) The operative enters the job number and the time spent on that job, manually throughout the day. I have arranged it so that any job number, if repeated later in the day, does not appear again in the column P, as I want now to accumulate all the times spent on that or any other particular job to be shown against that job number in Column P.
The problem I face is adding up the times (which are in column H) so that they appear next to the job number that now appears only once in Column P. That way, I can gather all the time spent by each operative on any job automatically, without having to manually add up all the times against every job. I hope that's clear.
How do I put, in (say) column Q the total time spent on that job number throughout the day, for each of the job numbers? Note where there are blank cells in Column P, this is where the job number is already repeated earlier in the day.Daily-Timesheet--RA-version--r2.xlsx
Comment
Watch Question

Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
=SUMIF($C$12:$C$44,C12,$H$12:$H$44)
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are you manually entering the formulas that appear in column 'P'? Also why are columns 'L' and 'M' the same?
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
You can also enter this formula in P12 and then copy down

=IF(ISERROR(MATCH(C12,P11:$P$11,0)),C12,"")

Author

Commented:
Hi. No, Column P has a formula developed from Column M - what you see here is the result of my fiddling around. I could have gone into Col P with a formula related to Col C.

Author

Commented:
Hi Saqib,
The =IF(ISERROR(MATCH(C12,P11:$P$11,0)),C12,"") formula is much more efficent than mine, but what I want to do is accumulate the hours against the job number. Your formula just gives me the job numbers again, doesn't it?
gowflowPartner
CERTIFIED EXPERT

Commented:
Put this in Q12 and drag down
=SUMIFS($H$12:$H$44,$C$12:$C$44,"="&P12)

Open in new window


Check the file
Gowflow
Daily-Timesheet--RA-version--r2--1-.xlsx
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
I have given you two formulas. The first one is for column Q and the second one is for column P.

Author

Commented:
I think maybe I phrased my question badly. For now, forget Column P: All that Column was for was to eliminate duplicates, so for  example 'GOW' is only stated once, whereas it occurs 7 times. If you look at Column C, what I want to do is accumulate the hours against (say 'GOW' - or any other entry), and put that numnber against the 'GOW (or other number) entry in Column P. The timesheet could have any entry whatsoever in Column C, and I want all those entries to be added up and shown in Column Q against the relevant entry in Column P. So in Q17 should appear 1:59 (1 hour 59 mins). This needs to work wjhatever job number is put into Column C, which gets carried over into Column P, where the idea is that each job number appears only once and accumulates the total time against that job number.
gowflowPartner
CERTIFIED EXPERT

Commented:
@BlosMusic You did not phrase your question badly. Was very clear.
Have you checked my solution ?
Gowflow
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Did you try Both formulas?

Column Q    =SUMIF($C$12:$C$44,C12,$H$12:$H$44)
Sorry, the column Q should have been
Column Q    =SUMIF($C$12:$C$44,P12,$H$12:$H$44)



Column P    =IF(ISERROR(MATCH(C12,P11:$P$11,0)),C12,"")

Author

Commented:
Hi GowFlow - yes!! That's done it! I shall check thoroughly, but I think you hit it! Thanks to all . . .any further thoughts or requests I shall get back. Thanks Saqib, Martin and GowFlow.
Partner
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks to all, but to GowFlow in particular. Nice solution and it works well. Thanks very much.
gowflowPartner
CERTIFIED EXPERT

Commented:
Your welcome anytime.
Gowflow

Author

Commented:
Thanks. Keep safe!
gowflowPartner
CERTIFIED EXPERT

Commented:
you too please feel free to post here any link to any question you may need help with i will be glad to assist.
Gowflow

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.