+ Reply to Thread
Results 1 to 8 of 8

Adding the totals over a date criteria

  1. #1
    Registered User
    Join Date
    08-28-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    2

    Cool Adding the totals over a date criteria

    I would like a formula to count the number of hours worked by various resources each month. What formula do I use to pick up the total number of hours that resource C works in January, February etc

    DATE RESOURCE HOURS
    01/01/14 C 5
    05/01/14 C 8
    14/01/14 A 9
    14/01/14 B 10
    20/01/14 C 11
    06/02/14 C 9
    09/02/14 C 4
    13/02/14 A 7
    16/02/14 B 14
    23/02/14 C 15

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Adding the totals over a date criteria

    d2=month(a2)

    and drag down

    After that use a pivot table to get your result.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Adding the totals over a date criteria

    Hi rio2014,
    Welcome to the Forum.
    Please find the attached sheet with two setups to see which one suits you.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Adding the totals over a date criteria

    No need for the extra column . If the data is formatted correctly the PT will allow grouping by months

  5. #5
    Registered User
    Join Date
    08-28-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    2

    Re: Adding the totals over a date criteria

    wow thank you so much. i spent all day at work trying to figure it out. with 2nd setup, where you have the month in the formula ''mmmm'', i need to differentiate by year also so it can tell the difference between january 2014 and january 2015....what would i put in there? eg ''mmmmyyyy''?

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Adding the totals over a date criteria

    Please find the attached sheet to see one way to achieve this. See if this helps.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Adding the totals over a date criteria

    or in adition to #2

    or e2=year(a2)

    and drag down

    After that use a pivot table to analyze your data.

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Adding the totals over a date criteria

    Again no extra column needed, the PT does the work for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. adding totals up
    By penguinJen in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-30-2013, 07:00 PM
  2. [SOLVED] Adding totals that meet a text and a data criteria
    By Telescope in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-05-2013, 02:57 PM
  3. Adding figures within range if date criteria is met
    By txt_mess in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2010, 11:48 AM
  4. Replies: 6
    Last Post: 07-29-2009, 03:45 PM
  5. Adding Totals in a worksheet
    By josterfeld in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-17-2008, 08:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1