Sum Unique Value based on different Time criterias

[Gurzil]

Hi everyone, I have an excel problem that I am stuck on, appreciate any help!

Goal: To identify the total sum of each company in a specific stage based on a Date criteria. For example:

[TABLE="width: 472"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Client[/TD]
[TD]Action Date[/TD]
[TD]Stage[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]1/1/2019[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]3/1/2019[/TD]
[TD]2[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]5/1/2019[/TD]
[TD]3[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]8/1/2019[/TD]
[TD]4[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]2/1/2019[/TD]
[TD]1[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]2/24/2019[/TD]
[TD]2[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]5/5/2019[/TD]
[TD]3[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]7/5/2019[/TD]
[TD]4[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]8/1/2019[/TD]
[TD]5[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]6/1/2019[/TD]
[TD]1[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]6/25/2019[/TD]
[TD]2[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]9/1/2019[/TD]
[TD]1[/TD]
[TD]$200[/TD]
[/TR]
</tbody>[/TABLE]


Desired Results:

Stage 4/15/2019 7/1/2019 10/1/2019
1 $0 $0 $200
2 $600 $200 $0
3 $0 $600 $0
4 $0 $0 $100
5 $0 $0 $500

In summary, the value of each Company should only be counted once based on the highest Stage that the Company is in based on the Date or period in time that I am looking at. I tried moving the data around, or using a Max function to pick up the highest date under the Action Date, but then I run into the issue of having multiple Company names, etc etc that limits me. Essentially I want excel to pick up the very latest Action Date that is less than the Date I am looking at and tell me the dollar amount in that current stage. As shown above, if I want to see what the value is by each current Stage as of 4/15/2019, then Company A has $100 in Stage 2, and Company B has $500 in Stage 2. If anyone has any advice on how to achieve this, I'd deeply appreciate it. Thank you all!
[TABLE="width: 421"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

your Result has nothing to do with your source data
maybe try Pivot Table or Power Query and Pivot Table

 

[Gurzil]

Thank you, will try that.
Also, it seems my desired results are not showing up the way I intended it to, let me see if this works:

[TABLE="width: 472"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]Stage[/TD]
[TD="align: right"]4/15/2019[/TD]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]10/1/2019[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

your dates Result vs Source are not compatible or your description isn't clear enough

 

[Gurzil]

ahh thats my bad. Basically what I was trying to say is that the source data time stamps every time a Company changes Stage, so that data is fixed.

If I want to see what the value is, per stage, at any given moment in time. How do I easily calculate that? For example, if I want to know the value of each Stage as of 7/1/2019, then I would look through my source data for all rows that have an Action Date "<= 7/1/2019", and out of that I want to pick the latest date for each Company, and identify which stage it's currently in and the value at that stage.

its like saying on 7/1/2019, I know Company A is in Stage 3 and valued at $100 because the very last action taken by Company A was 5/1/2019 which is less than or equal to 7/1/2019. Please let me know if this explanation makes more sense and thank you for taking the time to look at it.

 

[sandy666]

simply show the proper result based on your source example