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

Microsoft ExcelMicrosoft Office

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

=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

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.

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?

The =IF(ISERROR(MATCH(C12,P11:

gowflow

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

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.

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

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

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

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.

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

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.

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 questionBlosMusic

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

Gowflow

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

Gowflow

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck