Sum every nth column with moving date range

[Excelski]

Hi Everyone,

I would like to nth column with a moving date range
Please see attached

Any help greatly appreciated

Thanks

Kevin

[samba_ravi]

Everu 1st 14 500
Everu 2nd 14 700
Everu 3rd 14 900

Everu 1st 21 1200
Everu 2nd 21 1500
Everu 3rd 21 1800

how it comes, what is the logic for this

[Excelski]

Hi Siva,

Every date has 3 columns, it is the sum of every 1st column within a date range and every 2nd etc.

[samba_ravi]

d14=IF($B14="","",SUMPRODUCT(($C$9:INDEX($C$9:$AC$9,COUNTIF($B$14:$B14,$B14)*3))*($C$6:INDEX($C$6:$AC$6,COUNTIF($B$14:$B14,$B14)*3)=MOD(ROWS(E$14:E14),4))))
try this and copy towards down

[samba_ravi]

or
B14=LOOKUP(MOD(ROWS(B$14:B14)-1,4)+1,{1,2,3,4},{"Everu 1st","Everu 2nd","Everu 3rd",""})
C14=IF(B14="","",D$2*CEILING(ROWS(B$14:B14),4)/4)
D14=IF($B14="","",INDEX($C$9:$AC$9,COUNT($C$14:$C14))+IF(ROWS(D14:D14)<4,OFFSET(D14,-4,)))
Try this and copy towards down

[Excelski]

Hi Siva,

Thank you, it works but not the way i would like it to.

When I adjust the date range by changing D2 I would like to see my expected results in B27:D27, so B27 would display the sum of all the 1st cells within the date range B28 the 2nd and B29 the 3rd

sorry if i wasn't clear in post 1

Thanks for looking

Kevin

[samba_ravi]

Please reattach file with one example with expected result

[Excelski]

I have attached the file showing results for 21 day date range, if the range were 7 days (adjusted in D2) the results would be displayed within the same cells but different values as show on sheet

Thanks

[Indi_Ra]

Hi,

In B27 put this formula:

=SUMPRODUCT(($C$6:$AC$6=COLUMN()-1)*($C$8:$AC$8>=$C$2)*($C$8:$AC$8<$E$2)*$C$9:$AC$9)

then drag to right.

[samba_ravi]

B27=SUMIFS($C$9:$AC$9,$C$8:$AC$8,">="&$C$2,$C$8:$AC$8,"<="&$E$2-1,$C$6:$AC$6,C$6)
Try this and copy towards right

[Excelski]

Thanks Siva it works great, thanks for your help

Indi_Ra Thank you, works too

Kevin