Hi,
I have a worksheet of data with criteria in several columns
I am trying to do a sumifs of one column which lies on another sheet based on mutliple criteria, but with the ability to change the range the sumifs look at depending on the periods I want to analyse
The raw data will extend downwards as the year goes on and we add to it each month, but as the number of additional rows may vary, I have looked up the start and end rows for each period to use as an INDIRECT for the SUMIFS range (e.g Period 1 to Period 4 would looke at column $AE1:$AE2305, Period 2 to Period 4 would look at $AE1221:$AE3576,...)
However I realised that SUMIFS cannot handle a range like $AE1:$AE2305, only $AE;$AE, and therefore returns a #VALUE! error
Can anyone please help with a workaround and save my hair?
Many thanks!
I have a worksheet of data with criteria in several columns
I am trying to do a sumifs of one column which lies on another sheet based on mutliple criteria, but with the ability to change the range the sumifs look at depending on the periods I want to analyse
The raw data will extend downwards as the year goes on and we add to it each month, but as the number of additional rows may vary, I have looked up the start and end rows for each period to use as an INDIRECT for the SUMIFS range (e.g Period 1 to Period 4 would looke at column $AE1:$AE2305, Period 2 to Period 4 would look at $AE1221:$AE3576,...)
However I realised that SUMIFS cannot handle a range like $AE1:$AE2305, only $AE;$AE, and therefore returns a #VALUE! error
Can anyone please help with a workaround and save my hair?
Many thanks!
