Forum Discussion

SubhasisB's avatar
SubhasisB
Copper Contributor
Jul 06, 2022
Solved

Sumproduct with Offset to get Ytd Value

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...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 06, 2022

    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.

SubhasisB's avatar
SubhasisB
Copper Contributor
Jul 07, 2022
Thanks, it really works fine.
Thank You so much.

Resources