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:
<tbody>
</tbody>
Sheet B:
<tbody>
</tbody>
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!!!!!
Essentially, I am trying to pull cost data (see Sheet B).
Sheet A:
A -Unique Identifiers | B -Admit Date (l) | C -Discharge Date (l) | D - Cost | |
1 | 123456789 | 1/2/00 | 1/2/00 | $100 |
2 | 123456789 | 1/2/00 | 1/15/00 | $100,000 |
3 | 123456789 | 2/1/00 | 2/1/0 | $100 |
4 | 987654321 | 1/1/00 | 1/1/00 | $100 |
<tbody>
</tbody>
Sheet B:
A -Unique Identifiers | B - Admit Date (s) | C -Discharge Date (s) | D -Cost | |
1 | 123456789 | 1/1/00 | 1/14/00 | = |
2 | 987654321 | 1/30/00 | 2/3/00 | = |
<tbody>
</tbody>
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!!!!!
Last edited: