Forum Discussion

kayleigh1975's avatar
kayleigh1975
Copper Contributor
Aug 07, 2024

EDATES and SUMIFS Help

Hi All, 

I have created a spreadsheet showing all frequent costs of my company which has columns for frequency, date and description.

On my second sheet I have the dates for 6 months across the top row and the frequency and description in the first column.

 

To populate the data in the first month I have input a simple SUMIF based on the date and the description of my frequent cost sheet.

 

For the following 11 months, I wanted to do a SUMIF based on the date in the first month.

For example:

SUMIF(AA$2:BE$2,EDATE(BF$2,-1),AA12:BE12),0)

AA$2:BE$2 are my date rows (range)

BF$2 is my current date (criteria)

AA12:BE12 are my costs (sum range) the issue I'm running into is if there is a cost on the 31st August, it will not pick it up on the 30th September. 

 

Any idea how to fix this?

 

Thanks

excel
Formulas and Functions

1 Reply

Sort By
  • djclements's avatar
    djclements
    Bronze Contributor
    Aug 08, 2024

    kayleigh1975 Perhaps something along these lines might help:

     

    =SUMIF(AA$2:BE$2,IF(BF$2=EOMONTH(BF$2,0),EOMONTH(BF$2,-1),EDATE(BF$2,-1)),AA12:BE12)

     

    However, it's not very clear what should happen if there is a cost on both Aug 30th and Aug 31st. Should Sep 30th ignore Aug 30th? Or how about if there's a cost entered on Aug 30th but not on Aug 31st?

Resources