Year to Date Cost Savings

edaniel202

New Member
Joined
Mar 17, 2020
Messages
1
Office Version
2019
Platform
Windows
Hello, I'm trying to calculate year to date costs based on monthly costs. What formula do I use to calculate year to date costs? Any help would be greatly appreciated.

Thanks,
Emily

TaskTarget Completion MonthStatusDate CompletedMonthly CostTotal Cost Year to Date
Sample 1
1​
Complete1/10/2020
100​
Sample 2
1​
Complete1/28/2020
4500​
Sample 3
1​
Complete1/31/2020
2350​
Sample 4
1​
Complete1/15/2020
1375​
Sample 5
4​
In-ProcessToday's Date
266.66​
Sample 6
4​
In-Process
1333​
Sample 7
7​
In-Process
5306​
Sample 8
6​
In-Process
745​
Sample 9
5​
In-Process
900​
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Qadah

New Member
Joined
Sep 5, 2013
Messages
12
Hi Edaniel202

some parts (and structure) of your sample sheet are not very clear (to me at least), If my understanding is correct, you want to calculate (sum) Cost to Date for competed projects only

Question: you have target month, do you want to compare against target month? for example, if Sample 5 (target month is 4) was not done and today is in May, then it wouldn't be counted?

Else, generally, there are two ways to do it, either in the last column, ( I renamed it, check below), or with a sum cell below your column, check and let me know if you have any questions


TaskTarget Completion MonthStatusDate CompletedMonthly CostAccumulative Cost Year to DateToday's Date
18/03/2020​
=TODAY()
Sample 11Complete
10/01/2020​
100
100​
=IF(C2="Complete",E2+IF(ISNUMBER(F1),F1,0),"")
Sample 21Complete
28/01/2020​
4500
4600​
Sample 31Complete
31/01/2020​
2350
6950​
Sample 41Complete
15/01/2020​
1375
8325​
Sample 54In-Process266.66
Sample 64In-Process1333
Sample 77In-Process5306
Sample 86In-Process745
Sample 95In-Process900
Total Cost to Date
8325​
=SUMIFS(E2:E10,C2:C10,"Complete",D2:D10,"<"&I1)
8325​
=SUMIFS(E2:E10,C2:C10,"Complete",D2:D10,"<"&TODAY())
 

Watch MrExcel Video

Forum statistics

Threads
1,098,859
Messages
5,465,111
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top