Auto-fill by sections or conditional on another cell

Question

I have data that looks like this:

Class    Date   Yr1   Yr2   SeasonalityIndex
AUS     9-Aug     1     0        0.060465116
AUS    16-Aug     6     0        0.362790698
AUS    23-Aug     2     1        0.181395349
BVE     9-Aug     2     0                  ?

I want to auto-fill the Seasonality Index Column. However, the code for those cells looks like this:

=(D55+E55)/SUM($D$55:$E$67)*$H$1

Where H1 is a constant, but as you see each index is dependent on a SUM of all the values in that CLASS for year 1(Yr1) and year 2(Yr2). When Class changes, I want the auto-fill to change to the next section.

Obviously, a normal auto-fill would not work in this case because the cells are locked. Each section is exactly 13 rows if that helps.

Answer 1

I'm not sure if this will be workable for you, but you could try: 1.Name each range with the exact same name as the name of the class. 2. Replace the sum with: =(D55+E55)/SUM(INDIRECT(A55))*$H$1 assuming A55 is the cell with the name of the class in it.

Of course, naming the ranges first is a bit of a pain, depends how much you use this spreadsheet and whether it's being reused in the exact same format frequently.

Answer 2

If you want an expression that evaluates to SUM($D$55:$E$67) when it’s evaluated on rows 55-67, and evaluates to SUM($D$68:$E$80) when it’s evaluated on rows 68-80, etc., use

SUM(OFFSET($D$3, 13*INT((ROW()-3)/13), 0, 13, 2))

(Insert this into your full formula.)  Row numbers 55 through 67 get converted to 52 through 64 (by subtracting 3), which yield 4 through 4.923 (by dividing by 13), all of which get converted to 4 by INT().  Then multiply by 13, to get back up to 52, and use that as an offset to D3 to get up to D55.

---

Or, to add the values in Columns D and E for rows having the same value in Column A as the current row, use

(SUMIF($A$1:$A$999, $A1, $D$1)+SUMIF($A$1:$A$999, $A1, $E$1))

which does pretty much exactly what I just described.  Replace 1 and 999 with the range of rows in which you have class data.

It seems like it should be possible to combine the above into a single SUMIF (or maybe SUMIFS) that sums Columns D and E, but I couldn’t figure it out.