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
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
=SUMIF($C$12:$C$44,C12,$H$12:$H$44)
Are you manually entering the formulas that appear in column 'P'? Also why are columns 'L' and 'M' the same?
You can also enter this formula in P12 and then copy down
=IF(ISERROR(MATCH(C12,P11:$P$11,0)),C12,"")
=IF(ISERROR(MATCH(C12,P11:$P$11,0)),C12,"")
ASKER
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
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?
The =IF(ISERROR(MATCH(C12,P11:
Put this in Q12 and drag down
Check the file
Gowflow
Daily-Timesheet--RA-version--r2--1-.xlsx
=SUMIFS($H$12:$H$44,$C$12:$C$44,"="&P12)
Check the file
Gowflow
Daily-Timesheet--RA-version--r2--1-.xlsx
I have given you two formulas. The first one is for column Q and the second one is for column P.
ASKER
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.
@BlosMusic You did not phrase your question badly. Was very clear.
Have you checked my solution ?
Gowflow
Have you checked my solution ?
Gowflow
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,"")
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all, but to GowFlow in particular. Nice solution and it works well. Thanks very much.
Your welcome anytime.
Gowflow
Gowflow
ASKER
Thanks. Keep safe!
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
Gowflow