• 100% Satisfaction Guarantee

Kamil Anwar, Office Specialist
Category: Microsoft Office
Satisfied Customers: 4258
Experience:  8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.
69350257
Type Your Microsoft Office Question Here...
Kamil Anwar is online now

# Could you please assist in a formula to calculate hours in

### Resolved Question:

Could you please assist in a formula to calculate hours in a day and breaking down to an individual activity? For example in one column I will have the activity e.g. auditing, safety walks etc. In another column I will have the hours spent on that activity. I need a formula which can calculate the hours by reading and placing with that activity so I can compile total hours to that activity for a week or year etc.
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  Kamil Anwar replied 2 years ago.

Kamil Anwar :

Hello & Welcome to JustAnswer. My name is XXXXX XXXXX i will do my best to assist you with this question.

Kamil Anwar :

I will need to take a look at the document to write a formula to fit your requirements.

Customer:

Were do I send

Kamil Anwar :

Kamil Anwar :

I cannot download this can you copy the other link shown there or the file id shown.

Customer:

Kamil Anwar :

Thanks - Please give me a few minutes to check this.

Customer:

You will see in Cell B44 my attempt to write a formula

Kamil Anwar :

I am reconfiguring my office, please give me a moment.

Kamil Anwar :

I am extremely sorry for the wait but i am having problems with the Office. I am trying to fix it, will take a few minutes.

Customer:

Ok

Kamil Anwar :

=SUMPRODUCT((D2:D6="Drills / Exercises")*(C2:C6=""))

Kamil Anwar :

I am in the file now.

Kamil Anwar :

The Activity is in Column A

Kamil Anwar :

The Date is in Column B

Kamil Anwar :

The Hours are in Column C

Customer:

Activity is under Cells marked in red titled Task D,G,H etc I need the formula to identify the activity and place the hours accumilated so I can build a charts to show the figures for weekly and monthly reports

Kamil Anwar :

Is there a condition on which basis to identify the activity?

Customer:

I don't believe I will need to capture the dates

Customer:

Not sure what you mean by condition

Kamil Anwar :

You stated you need a formula to identify the activity and place the hours accumulated so you build a weekly chart.

Customer:

Basically if the formula reads auditing and then 3 hours I need the 3 hours to go to auditing and to calculate as I move across the spread sheet in which I can build an accumulative total over a set period of time.

Kamil Anwar :

If we say

2 hours spent on activity A
2 hours spent on activity B
3 hours spent on activity D

In a day.

Kamil Anwar :

Do you want the total for each activity (for that day to go under the listed activities at the bottom of the sheet)?

Customer:

When you reference A,B,& D are refering to Cells or an example?

Kamil Anwar :

Just an example

Kamil Anwar :

So i can understand it more clearly

Customer:

Yes that what I would be looking for so those hours are broken down when the formula identifies activity and puts the hours with it to create a total over a period of time.

Kamil Anwar :

What would that period of time be (week) ?

Customer:

A week would be good but I probably will end up going into a month. If we do it as a week I can make a formula to accumalate over the month

Kamil Anwar :

Okay. Let me try something.

Kamil Anwar :

What is column A for?

Customer:

They are for break down for each work site. I was going to repeat the formula for each work site to read the required cells in there areas. This is unless your formula will pick up work sites as well?

Kamil Anwar :

Thanks for the information. I am working on it to get the output now

Kamil Anwar :

Test.xlsx

Kamil Anwar :

I have simplified it so that we can prepare a correct template.

Customer:

A heap of folder came up when I down loaded. Did you send the spread sheet back?

Kamil Anwar :

Yes

Kamil Anwar :

It is called test.xlsx

Kamil Anwar :

Customer:

Comes up with folders

Kamil Anwar :

Whats the name of the folder?

Customer:

Cannot find tests

Customer:

_rels, docProps,xl

Kamil Anwar :

Is it after you click test.xlsx?

Customer:

Kamil Anwar :

Strange, it's working here. Let me upload it on a different site.

Customer:

you can send [email protected]

Kamil Anwar :

I can't see your email as it's filtered. It is against the site policy to contact on e-mail, unfortunately.

Customer:

There may be filters as I'm on a work server, not sure

Kamil Anwar :

Kamil Anwar :

I guess that's the issue then which is why you are not able to view the file.

Customer:

When I click on test it just highlights, so i then hit download and it gives me the folders mentioned

Kamil Anwar :

Let me add the file to zip

Kamil Anwar :

Customer:

Access is blocked for this site

Kamil Anwar :

Are you trying to open the file using Excel?

Kamil Anwar :

Can you open Excel first and then try and open the test.xlsx file from within the Excel.

Customer:

I'm getting blocked before getting a chance to open. the only one I have been able to open is wiki

Kamil Anwar :

Kamil Anwar :

When you open the wiki site.

Kamil Anwar :

opened*

Kamil Anwar :
Kamil Anwar :

Can you click the Full Size Image to see if it downloads the file?

Customer:

I have opened and looking now

Kamil Anwar :

Great

Customer:

Yes that is a bit to basic, I will need to spread the hours over the dates not as a day 1,2,3 ect. I will be adding comments under the dates.

Kamil Anwar :

We can change the days with the dates - That is just to clarify the requirements.

Kamil Anwar :

You will input the hours under a date.

Customer:

You will see that I have place the activities under drop boxes this so when filling in daily comments I don't have the full list of activities listed to reduce the page size. If we could get the formula to regognise the activity and then add those hours to it which I will do under hiden cells so I can develop charts

Kamil Anwar :

That means, Formula should first identify the task, then get the hour and the sum it up (to get a weekly total)

Customer:

yes

Kamil Anwar :

Since both the columns C & D are unrelated - It will not be possible to achieve this with limited excel functionality. There has to be a bridge or reference to a column with the value in order to get this done.

Kamil Anwar :

How many tasks/activities are there in total?

Kamil Anwar :

Also logically, the drop down gives you the ability to select same many time which can conflict.

Customer:

=SUMIF(D2:D6,"=Drills / Exercises",C2:C6). Used this formula which seems to work to acheive the information needed.

Kamil Anwar :

This brings the total for each day only, based on this we can try and get weekly / monthly totals.

Kamil Anwar :

=SUMIF(D2:D6,"=Drills / Exercises",C2:C6)+SUMIF(J2:J6,"=Drills / Exercises",I2:I6)

Customer:

=SUMIF(D2:FB6,"=Auditing",C2:C6). I put this in which calculates to the end of next month. I can change the first part to suit time frames.

Kamil Anwar :

I guess you've already found the solution.

Customer:

I think this is what I'm looking for. I'm putting the formula in now to run a test

Kamil Anwar :

Great. I am also looking for more options.

Customer:

Cheers

Kamil Anwar :

It seems like you have gone offline. Kindly let me know the results. Thanks

Kamil Anwar :

Are you still there?

Customer:

Working well

Kamil Anwar : Excellent. I have unlocked the rating panel so you can rate me. If you need further assistance please let me know.
Kamil Anwar, Office Specialist
Category: Microsoft Office
Satisfied Customers: 4258
Experience: 8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

### What Customers are Saying:

• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Previous | Next >
• My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
• Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
• This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
• Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
• I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
• Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
• Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland

• ### jstinehelfer

#### Satisfied Customers:

36
A+ Comptia Certified computer repair
< Last | Next >

### jstinehelfer

#### Satisfied Customers:

36
A+ Comptia Certified computer repair

### JasonJames122

#### Satisfied Customers:

0
I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

### Jess M.

#### Satisfied Customers:

481
Computer Software Specialist for more than 10 years

### The-PC-Guy

#### Satisfied Customers:

450
20 years experience providing remote computer support

### James K.

#### Satisfied Customers:

260
Technical Director of IT Company

### IT Miro

#### Satisfied Customers:

147
Bachelor's Degree in Information Technology, Microsoft Certified Professional