Hi,
I'm trying to return a value only if date criteria are satisfied, and I'm struggling to do this using the SUMIFS formula. I'm an ok excel user but I don't often have a need to use functions beyond vlookups, pivots etc.
I have a number of projects and I am trying to split the cost of projects across the life of those projects. Each will have a start date and an end date.
I would like automatic formula each month to pull through what the monthly cost will be. E.g. on the example below:
- In April 23 and May 23 the monthly cost would be 0 because the project does not start until June
- From June 23-December 23 the monthly cost would return a value of 140,845 because the project is ongoing in this period
- In January 24 the monthly cost would return a value of 0 because the project is finished
Does anyone know what formula I can use to make this information populate automatically?
Thanks!
=IF(AND(D2>=$B$2,D2<=$B$3),$B$1/(DATEDIF($B$2,$B$3,"m")+1),0)This returns the expected result if i correctly understand what you want to do.
