Hello & Welcome to JustAnswer. My name is XXXXX XXXXX i will do my best to assist you with this question.
I will need to take a look at the document to write a formula to fit your requirements.
Were do I send
You can upload it to www.wikisend.com and provide me with the link to download it.
Copy of P'nyang Daily Report.xlsx
I cannot download this can you copy the other link shown there or the file id shown.
http://wikisend.com/download/126604/Copy of P'nyang Daily Report.xlsx
Thanks - Please give me a few minutes to check this.
You will see in Cell B44 my attempt to write a formula
I am reconfiguring my office, please give me a moment.
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.
=SUMPRODUCT((D2:D6="Drills / Exercises")*(C2:C6=""))
I am in the file now.
The Activity is in Column A
The Date is in Column B
The Hours are in Column C
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
Is there a condition on which basis to identify the activity?
I don't believe I will need to capture the dates
Not sure what you mean by condition
You stated you need a formula to identify the activity and place the hours accumulated so you build a weekly chart.
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.
If we say2 hours spent on activity A2 hours spent on activity B3 hours spent on activity DIn a day.
Do you want the total for each activity (for that day to go under the listed activities at the bottom of the sheet)?
When you reference A,B,& D are refering to Cells or an example?
Just an example
So i can understand it more clearly
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.
What would that period of time be (week) ?
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
Okay. Let me try something.
What is column A for?
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?
Thanks for the information. I am working on it to get the output now
Please check this: Test.xlsx
I have simplified it so that we can prepare a correct template.
A heap of folder came up when I down loaded. Did you send the spread sheet back?
It is called test.xlsx
Click Here to download
Comes up with folders
Whats the name of the folder?
Cannot find tests
Is it after you click test.xlsx?
I hit download files and thats what is coming up
Strange, it's working here. Let me upload it on a different site.
you can send XXXXX@XXXXXX.XXX
I can't see your email as it's filtered. It is against the site policy to contact on e-mail, unfortunately.
There may be filters as I'm on a work server, not sure
Click Here to download from a different site.
I guess that's the issue then which is why you are not able to view the file.
When I click on test it just highlights, so i then hit download and it gives me the folders mentioned
Let me add the file to zip
Click Here to go to the download site.
Access is blocked for this site
Are you trying to open the file using Excel?
Can you open Excel first and then try and open the test.xlsx file from within the Excel.
I'm getting blocked before getting a chance to open. the only one I have been able to open is wiki
But it did download the file before?
When you open the wiki site.
Can you click the Full Size Image to see if it downloads the file?
I have opened and looking now
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.
We can change the days with the dates - That is just to clarify the requirements.
You will input the hours under a date.
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
That means, Formula should first identify the task, then get the hour and the sum it up (to get a weekly total)
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.
How many tasks/activities are there in total?
Also logically, the drop down gives you the ability to select same many time which can conflict.
=SUMIF(D2:D6,"=Drills / Exercises",C2:C6). Used this formula which seems to work to acheive the information needed.
This brings the total for each day only, based on this we can try and get weekly / monthly totals.
=SUMIF(D2:D6,"=Drills / Exercises",C2:C6)+SUMIF(J2:J6,"=Drills / Exercises",I2:I6)
=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.
I guess you've already found the solution.
I think this is what I'm looking for. I'm putting the formula in now to run a test
Great. I am also looking for more options.
It seems like you have gone offline. Kindly let me know the results. Thanks
Are you still there?