Sum Unique Value based on different Time criterias

Gurzil

New Member
Joined
Oct 4, 2019
Messages
3
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:

ClientAction DateStageAmount
Company A1/1/20191$100
Company A3/1/20192$100
Company A5/1/20193$100
Company A8/1/20194$100
Company B2/1/20191$500
Company B2/24/20192$500
Company B5/5/20193$500
Company B7/5/20194$500
Company B8/1/20195$500
Company C6/1/20191$200
Company C6/25/20192$200
Company C9/1/20191$200

<colgroup><col span="4"></colgroup><tbody>
</tbody>


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!

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
your Result has nothing to do with your source data
maybe try Pivot Table or Power Query and Pivot Table
 
Last edited:
Upvote 0
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:

Stage4/15/20197/1/201910/1/2019
100200
26002000
306000
400100
500500

<colgroup><col span="4"></colgroup><tbody>
</tbody>
 
Upvote 0
your dates Result vs Source are not compatible or your description isn't clear enough
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top