Extremely Challenging Problem

[spm2116]

So, I've come across the most difficult Excel problem I have ever seen. I'll let you be the judge. Please chime in, as I am really stuck.

Essentially, I am trying to pull cost data (see Sheet B).

Sheet A:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A -Unique Identifiers[/TD]
[TD]B -Admit Date (l)[/TD]
[TD]C -Discharge Date (l)[/TD]
[TD]D - Cost[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123456789[/TD]
[TD]1/2/00[/TD]
[TD]1/2/00[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123456789[/TD]
[TD]1/2/00[/TD]
[TD]1/15/00[/TD]
[TD]$100,000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123456789[/TD]
[TD]2/1/00[/TD]
[TD]2/1/0[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]987654321[/TD]
[TD]1/1/00[/TD]
[TD]1/1/00[/TD]
[TD]$100[/TD]
[/TR]
</tbody>[/TABLE]

Sheet B:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A -Unique Identifiers[/TD]
[TD]B - Admit Date (s)[/TD]
[TD]C -Discharge Date (s)[/TD]
[TD]D -Cost[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123456789[/TD]
[TD]1/1/00[/TD]
[TD]1/14/00[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]987654321[/TD]
[TD]1/30/00[/TD]
[TD]2/3/00[/TD]
[TD]=[/TD]
[/TR]
</tbody>[/TABLE]

So, I am looking for the cost (Sheet B, D:D). For any date that falls between the dates in Sheet B as those dates fall between the dates in Sheet A, I want that cost summed. In the example for 123456789, I want all of the costs that appear in Sheet A for the period of 1/1/00-1/14/00. So, the answer I would want in this example is $100,100. For my purposes, it is fine that Sheet B ends on 1/14/00 and Sheet A ends on 1/15/00, so long as I am pulling in All of the $100,000 (and the $100 from 1/2/00). The amount in B3 would need to be left out of the sum because it falls outside of the range in Sheet B.

I have a few thousand lines of data, so I can't do this by hand.

My initial thought was to do a SUMPRODUCT (nested in an IF statement) for a range lookup, but then I did not know how to pull in the costs. So then I tried an IFSUMS but I couldn't really make that work.

I'm using Excel 2010 (I think, or whatever the latest one is).

PLEASE HELP!!!!!

 

[Danerida]

SUMIFS should work. Try:

=SUMIFS('Sheet A'!D:D,'Sheet A'!A:A,'Sheet B'!A2,'Sheet A'!B:B,">="&'Sheet B'!B2,'Sheet A'!B:B,">="&'Sheet B'!B2)

 

[spm2116]

Hi Danerida,

This is very close. It pulls all of the cost from the Admit date and beyond, which would include data that is there after the discharge date. So from my above example, this formula will pull in the $100 from 2/1/00, which I would not want since that amount is outside of the window of the dates in SheetB. I'm not sure how to add that part in. I tried to add some more variables to the end, but it isn't working.

SUMIFS should work. Try:

=SUMIFS('Sheet A'!D:D,'Sheet A'!A:A,'Sheet B'!A2,'Sheet A'!B:B,">="&'Sheet B'!B2,'Sheet A'!B:B,">="&'Sheet B'!B2)

 

[Caribeiro77]

Assuming both of your sheets have header's in line 1, in sheet2 cell D2 try:

=SUMPRODUCT(--(Sheet1!$A$2:$A$5=Sheet2!A2),--(Sheet1!$B$2:$B$5>=Sheet2!B2),--(Sheet1!$C$2:$C$5<=Sheet2!C2),Sheet1!$D$2:$D$5)

and drag down...

 

[spm2116]

This was what I had originally tried, but Excel will not let me make the unique identifiers equal one another. Also, I'm assuming by Sheet1 you mean SheetB.

Hi Danerida,

This is very close. It pulls all of the cost from the Admit date and beyond, which would include data that is there after the discharge date. So from my above example, this formula will pull in the $100 from 2/1/00, which I would not want since that amount is outside of the window of the dates in SheetB. I'm not sure how to add that part in. I tried to add some more variables to the end, but it isn't working.

 

[Caribeiro77]

No, sheet1 =your sheetA, sheet2 =your SheetB

 

[spm2116]

Yes, that is what I meant. But it still does not work. I was thinking of adding to the ifsums the discharge dates.It is the long way around but since the Admit dates worked, why not the discharge dates.

No, sheet1 =your sheetA, sheet2 =your SheetB

 

[rayk8301]

I'm not the most experienced but it sounds like you could try powerpivot and create a relationship between the two tables?