SOLVED

Sumproduct with Offset to get Ytd Value

Occasional Contributor

Hi All,

I am trying to fetch the Ytd value of Volume and Value in the attached excel sheet in column AL & AM, so that if i change the input value in row AM1 then Ytd Volume and Value till that month should come.

I have used Sumproduct with Offset but it's adding up all till that month, i want seperately in both the columns AL & AM.

Pls can you help me in this regard.

 

 

Thanks and Regards,

Subhasis 

4 Replies

@SubhasisB  I think I know what you want and I used the row 3 instead of the 'helper' row 1 to identify which columns should be added and got rid of the OFFSET because I try to avoid using that function:

 

=SUMPRODUCT($B5:$AK5*($B$3:$AK$3<=$AM$1)*($B$3:$AK$3>0))

 

then by offsetting the "SUM" columns to the right I did the Value and Unit P Cumulatives.

BTW, the numbers may be off from what you 'expect' because you start April 2021 and then after Dec 2021 you go back to Jan 2021 instead of Jan 2022.  This formula therefore adds in the last 3 months because they are before the June 2021 date.

best response confirmed by Hans Vogelaar (MVP)
Solution

@SubhasisB 

That could be

=SUMPRODUCT(
   B5:INDEX($B5:AK5, 3*ROUNDUP( MATCH(AM$1,$B$1:AK$1,0)/3, 0) )*
  (MOD( COLUMN(  B5:INDEX( $B5:AK5, 3*ROUNDUP( MATCH(AM$1,$B$1:AK$1,0)/3, 0) ) ) - COLUMN($A$3)-1,3) = 0) )

for Volume and =1, =2 at the end for Value and Unit.

Thanks, it really works fine.
Thank You so much.

@SubhasisB , you are welcome