Forum Discussion

gorandimeski's avatar
gorandimeski
Copper Contributor
Feb 20, 2020

how to calculate sum of the previous months by company

Hello all,   my question regards the calculation of the sum of the working hours for each company in the previous months. I have a column with companies names, column with dates (01.01.2018, 01.02....
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 20, 2020

gorandimeski 

You may use SUMIFS, it's like

=SUMIFS(
  <hours range>,
  <dates range>,">"&EOMONTH(TODAY(),-12),
  <dates range>,"<="&EOMONTH(TODAY(),-1)
)

if you calculate 11 months back from todays' month.

gorandimeski's avatar
gorandimeski
Copper Contributor
Feb 22, 2020

SergeiBaklan 

Thanks Sergei, the formula works but it calculates the sums as cumulative (for example, months from January to November should be zero, right?), December needs to be calculated as a sum from January to November, January 2018 as a sum from February to December 2017 etc. All that needs to be calculated by company so I added company column as a criteria in the sumifs formula. Should I add more criteria to get 0 for the first eleven months or use another formula different from sumifs? Thanks

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 22, 2020

    gorandimeski 

    Perhaps in your case PivotTable with the measure which calculates sum for previous from row content month eleven months will be more suitable. But that's optional. With SUMIFS you may skip first 11 month if add one more criteria that date shall be out of MIN(dates)+11 months.

Resources