how to calculate sum of the previous months by company

Copper Contributor

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.2018, 01.01.2019...), and column with working hours (number). So, for each company I want to calculate the sum of the working hours in the previous 11 months (for example in December I want to calculate the sum of the working hours in the previous 11 months for each company) and the formula has to be dynamic.

I hope you understand my question and for any solution I will be more than thankful 

3 Replies

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

@Sergei Baklan 

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

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