Compare the date range and pull the values

[sindhuja]

Hi,
I need to compare the case ID in column F along with the From Date (column G) and To date (column H) with the case ID in column A with the From Date (column B) and To date (column C).
If the Date is within the range then we need to add the total.
Eg. For the case ID : A123570 the date range to compare is 05/19/2012 and 05/31/2012. If the range is found in column B and C for the case ID in column A then the corresponding value should be in column I.

Can I get assistance on this pls…

-Sindhuja

[GTO]

I may have this backwards, but could it be:
[vba]=SUMPRODUCT(--($A$2:$A$18=F2),--($B$2:$B$18<=G2),--($C$2:$C$18>=H2),$D$2:$D$18)[/vba]

(and drag the formula down)

[Bob Phillips]

You are going to have to explain how you get your predicted results. Taking A123570 as an example, the start and end dates of the data for that ID are 3rd May, with a value of 446.8. In your results table you have start and end dates of 19th and 31st May, both beyond the data date, and you show a result of 747.8. Where does that come from?

[sindhuja]

Hi,

I want the results to be entered in the column K. I have attached the updated spreadsheet with the expected results. This is to check the duplicates.

-sindhuja

[sindhuja]

Hi,

I want the results to be entered in the column K. I have attached the updated spreadsheet with the expected results. This is to check the duplicates.

-sindhuja

[Bob Phillips]

You haven't attempted to answer my questions, just come up with another set of numbers, you don't explain how they are derived, how you can have to dates that are after from dates etc.

[sindhuja]

The second attachment was the correct one..

Date range can be anything. I need to check whether the case ID in column F within the range, does not get duplicated.

As an example check the case ID A123571. Two entries of case ID A123571 in column F with different date range (5/7 - 5/8 and 5/2 and 5/9).

For which the date range in column B and C is 5/7 and 5/10. in first entry 5/7 and 5/8 is between the range 5/7 and 5/10. In the second entry 5/9 is between the range which means duplicated.

Hope am clear now .

-sindhuja