seaching for info between dates in a table

**wassonc** · 08-21-2009, 07:28 AM

Basically I have two groups of data

Group 1 is machine info and has

Serial Number Build Month Commission Month

Group 2 is warranty info and has

Serial Number Service Claim Value of Service Claim

What I need to do is for all machines built in a given month count the number of claims within a given number of months after commissioning and the total value of the claims.

Eg

If I have a machine serial number abc123 built in jan 07 and commissioned mar 07 I need to search the table of data in group 2 for the number of claims between mar 07 and may 07 and also the total value of those claims

If you have any suggestions it would be a great help.

Thanks

Colin

**NBVC** · 08-21-2009, 07:41 AM

Welcome to the forum,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**NBVC** · 08-21-2009, 08:42 AM

Does group 2 have any date column to search within?

**wassonc** · 08-21-2009, 09:13 AM

Sorry data tables are as follows

Table 1

Serial Number : Build Month : Commission Month

Table 2

Serial Number : Service Claim Date : Value of Service Claim

**NBVC** · 08-21-2009, 09:24 AM

You will need to use a Sumproduct() based formula for multiple conditions...

e.g.

To Count

Please Login or Register  to view this content.

To Sum:

Please Login or Register  to view this content.

Where

Table 2 sheet'!$A$1:$C$100 is the sheet and range the database is on.

Adjust ranges to suit.

**wassonc** · 08-21-2009, 11:20 AM

Seems to be working

Many thanks for your help.

Colin

**NBVC** · 08-21-2009, 11:47 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save