Avatar of BlosMusic
BlosMusic asked on

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

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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
gowflow

8/22/2022 - Mon
Saqib Husain

=SUMIF($C$12:$C$44,C12,$H$12:$H$44)
Martin Liss

Are you manually entering the formulas that appear in column 'P'? Also why are columns 'L' and 'M' the same?
Saqib Husain

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

=IF(ISERROR(MATCH(C12,P11:$P$11,0)),C12,"")
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
BlosMusic

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.
ASKER
BlosMusic

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?
gowflow

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Saqib Husain

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

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

@BlosMusic You did not phrase your question badly. Was very clear.
Have you checked my solution ?
Gowflow
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Saqib Husain

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,"")
ASKER
BlosMusic

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.
ASKER CERTIFIED SOLUTION
gowflow

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
BlosMusic

Thanks to all, but to GowFlow in particular. Nice solution and it works well. Thanks very much.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gowflow

Your welcome anytime.
Gowflow
ASKER
BlosMusic

Thanks. Keep safe!
gowflow

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck