Results 1 to 7 of 7
  1. #1
    stanlafayette
    Guest

    Date Formula (2000 / XP)

    =SUMPRODUCT(('F:2006Report 2006[Temp MAP_2.xls]Data'!$O$2:$O$31=$D35),('F:2006Report 2006[Temp MAP_2.xls]Data'!$P$2:$P$31=J$4))

    I use the above formula, which works great for a grand total, to get the count of instances the criteria matches in the worksheet. My question is: "What do I need to do to this formula, to make it get the data that only falls in a given MONTH, the dates on the DATA worksheet in TEMP MAP_2 are entered as 1/30/06,4/25/02 etc...I need to specify in the above formula to count only the entries that fall in January, or February, etc.......Hope I explained this correctly--- worksheet is too large to attach, thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Formula (2000 / XP)

    Could you explain which column contains what, or post a small sample workbook?

  3. #3
    stanlafayette
    Guest

    Re: Date Formula (2000 / XP)

    My problem is incorporating a date function with the sumproduct to separate the numbers for each person per each month. I need the formula for Bill to tally only totals with the date for January, for example. My actual formula looks in another workbook to get the results, and as I said the previously posted formula works well for this for grand totals but I can't sort by the month. thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Formula (2000 / XP)

    The easiest way is to create a pivot table with the date and name as row fields, and Apples, Oranges, Bananas and the total as data fields. Once the pivot table has been created, right-click the date field and group it by month. See the attached workbook.

    Added later: I forgot to mention that pivot tables aren't updated automatically. Click somewhere in the pivot table and select Data | Refresh Data or click the Refresh Data button on the Pivot Table toolbar to update it.

  5. #5
    stanlafayette
    Guest

    Re: Date Formula (2000 / XP)

    Thanks, I was hoping to use the formula I mentioned to automatically update the report when I opened the workbook. I'll explore the pivot table.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date Formula (2000 / XP)

    If you want a formula approach, you would have to check the month number, as per the attached. You will have to manually set up the tables for each month first.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    stanlafayette
    Guest

    Re: Date Formula (2000 / XP)

    Rory - that's exactly what I needed, thanks to you and Hans for the fast responses.

Posting Permissions

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