# Thread: Date Formula (2000 / XP)

1. ## 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. ## Re: Date Formula (2000 / XP)

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

3. ## 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. ## 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. ## 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. ## 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.

7. ## 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
•