Forum Discussion

mikep7's avatar
mikep7
Copper Contributor
Mar 17, 2023

Returning a value based on multiple criteria - SUMIF

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!

 

 

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 17, 2023

    mikep7 

    =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.

     

Resources