How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask ITguy25 Your Own Question
ITguy25
ITguy25, Database Administrator
Category: Programming
Satisfied Customers: 324
Experience:  10+ years computer experience. 5 year in IT Management. I manage multiple servers in a variety of OS
25315603
Type Your Programming Question Here...
ITguy25 is online now
A new question is answered every 9 seconds

excel problem- need help with formula In column B i have

This answer was rated:

excel problem- need help with formula

In column B i have dates in column D i have values (hours).

what i want to do is sum the values of each date seperately (so that i have a total of hours on 1/1/06 and then 1/2/06 and so on and so on without doing this manually- any formula suggestions greatly appreciated.

ITguy25 :

Hello, My name is XXXXX XXXXX I would be happy to help you with your formula in Excel.

ITguy25 :

Would you mind uploading your file so that I can take a look?

ITguy25 :

You can upload to a service such as speedyshare. http://www.speedyshare.com/, then copy/paste the link back here for me to view.

Customer:

i uploaded it with yousend it-- here you go-- thanks

ITguy25 :

Thank you. One moment while I take a look.

Customer:

i'm trying to get the total hours for each day- so that i have total hours worked for 1/2/07, total hours worked for 1/3/07 ,etc etc-- the forumla i have in there- is not working (sorry for the mass emails, i didn't realize that pressing enter sent it) thanks a ton

ITguy25 :

So, in another column, you would like a listing of the days and then the sum of hours for each day?

Customer:

yes! exactly- so that i have a sum of hours for each day so that i can look at it and say "ok, she worked over 12 hours, this many days etc etc" so that i have a sum of hours worked for each date-- thanks

ITguy25 :

Gotcha. One moment while I work on that, ok?

Customer:

yes-- thanks so much

Customer:

maybe a pivot table?

ITguy25 :

Just one more moment.

Customer:

thanks

ITguy25 :

Would this work? http://speedy.sh/GZTfH/Zoe-Littlepage-2007-Hours-with-forumula-with-pivot.xlsx

Customer:

i am continuing to work wiht this-- the date column was screwed up (the formatting) from where i ocr'd this from PDF to xls- i have fixed that issue- i'm uploading spreadsheet with that fixed incase that helps

ITguy25 :

It may. The sheet I just uploaded did what you wanted, I believe. My formula for unique values (to do it all in formula) was erroring out.

Customer:

i can't get this to download...

Customer:

can you email it to [email protected]

ITguy25 :

Let me use a different site. Your email is blanked out. We are not allowed to use direct email on the site.

ITguy25 :

One second I will try a different site.

Customer:

thanks

ITguy25 :

Let me know if that works.

Customer:

aprilleec

Customer:

yes-- can you tell me what you did?

ITguy25 :

There are two ways to do what you wanted. One is through pivot table and the other is through straight formula (Using countifs function) The pivot table is a better solution as it allows you to go back and sort/ select date ranges to filter, etc.

ITguy25 :

To make the pivot table work, you have to create header labels, as you can see I labeled "Date", "Description", "Hours".

ITguy25 :

You should see a drop down box on the date field with many option to filter, if you ever need to narrow it down to certain ranges.

Customer:

the dates say 2001 though- where my original dates say 2007?

Customer:

(thanks for your help though)

ITguy25 :

Let me look. It may be because the data in your original date field. One moment, ok?

ITguy25 :

In the excel sheet you gave me, that is how it is formatted. For example, in your original sheet, if you click on a date, you will see in the formula box, the actual date Excel is reading.

Customer:

thanks!

ITguy25 :

I can reformat those.

Customer:

thanks

Customer:

that would be amazinggggggggggggggggggggggggggg

ITguy25 :

Ok, please give me one moment.

Customer:

thanks!! i can't tell you how much help you are

ITguy25 :

One more moment. Almost done..

ITguy25 :

Sorry taking awhile. The way your excel sheet is, the dates are formatted differently, even half way through the page.

Customer:

thank you sooo much

ITguy25 :

Almost done. I am through august. I will leave the converting formula row I have built so you can see how they are all different. Very odd how it imported.

Customer:

thankssssss

ITguy25 :

You can erase columns I through L, that is just the stuff I had to do to convert each date.

ITguy25 :

Here is also another version, with the dates sorted in the pivot table:

Customer:

hey you have been aweosome--- thank you

ITguy25 and 3 other Programming Specialists are ready to help you

Related Programming Questions