Auto Sum an Additional Row Weekly

[gweno]

I have a set of sales forecast data. Column A would be the weeks (1-52), column B would be the forecast dollars for Orange sales, column C for Peach sales, Column D for Apple sales and column E for Pears. After 13 weeks, the spreadsheet sums the weeks by quarter total dollars so that row might need to be eliminated (ie cell B15 would be a sum of wk 1-13, B16 would be wk 14 sales, after the 2nd qtr another row sums up the 2nd qtr sales,etc). I have a Counter cell which I update which week we are in. So if I'm in week 18, I have a cell that needs the results of Orange, Peach, Apple and Pear forecast sales on a quarterly basis (wks 14-18 in that example) and another cell is looking for the results for the year to date (wks 1-18). I made up that example, but there will be situations in which I might need the result to be just the sum of Peaches (column C) and sometimes I might need to add 2 or more columns for the result (sum of peaches and pears on qtrly and ytd basis for example).

I have the same data below times multiple locations. I think I might need VB code.

I appreciate any help you can give me and I hope this makes sense!
​

 

[AlphaFrog]

CHOOSE(INT(($F$8-1)/13)+1,0,B$15,B$29,B$43)
F8 should be the cell with the week number

CHOOSE(INT(($F$8-1)/13)+1,0,B$15,B$29,B$43)
B$15,B$29,B$43 should be the cells with the quarterly subtotals.

Is this correct for your configuration?

 

[gweno]

I went back to the basics w/ the fruit and same results.. I copied/pasted the exact formula


I was wrong about the example of wk 37- the result is Qtr 1 (not 2) plus weeks 27-37.


here is my formula... I moved the counter
=SUMPRODUCT(--($A$2:$A$56<>"Total"),--($A$2:$A$56<=$A$58),B$2:B$56)-CHOOSE(INT(($A$58-1)/13)+1,0,B$15,B$29,B$43)

 

[AlphaFrog]

Try this instead...

=SUMPRODUCT(--($A$2:$A$56<>"Total"),--($A$2:$A$56<=$A$58),B$2:B$56)
 -SUMPRODUCT(--($A$2:$A$56<>"Total"),--($A$2:$A$56<=(INT(($A$58-1)/13)*13)),B$2:B$56)

 

[gweno]

oh yeah!!
thank a million times over! It will save me sooo much time!