sum columns with a dynamic range definition

[monoj chakraborty]

I am trying to add the columns in the file attached by quarter by using offset function with dynamic range reference. But unfortunately, I am not able to pass the column references as variable by filtering for year and then the months (e.g. 1-3 would be Q1, 4-6 would be Q2 and so on).

I have tried to show the formula that I have typed into the destination cell.

The worksheet is attached for a quick reference.

Is it also possible to use a sum as array function with search function filtering for "f16", "f17" etc., and then by 1,2,3 and so on for the months to arrive at a quarter sum?

Any help much appreciated

 

[bosco_yip]

Try..........

1] Select for "Brand" from A9 dropdown list

2] In B11, copied across right to E9 and all copied down :

=SUM(OFFSET($A$1,MATCH($A$9,$A$2:$A$7,0),MATCH($A11&"_1",$B$1:$AN$1,0)+(COLUMNS($A:A)-1)*3,,3))

3] In F11 copied down :

=SUM(B11:E11)

Regards
Bosco

 

[p45cal]

Another offering. In the vicinity of cell B13, array-entered:

=SUMPRODUCT(($B$1:$AN$1=$A13 & "_1")+($B$1:$AN$1=$A13 & "_2")+($B$1:$AN$1=$A13 & "_3"),INDEX($B$2:$AN$7,MATCH($A$12,$A$2:$A$7,0),))

and by way of a cross-check, at A22 and below a re-arrangement of the data with a pivot table.

 

[monoj chakraborty]

Try..........

1] Select for "Brand" from A9 dropdown list

2] In B11, copied across right to E9 and all copied down :

=SUM(OFFSET($A$1,MATCH($A$9,$A$2:$A$7,0),MATCH($A11&"_1",$B$1:$AN$1,0)+(COLUMNS($A:A)-1)*3,,3))

3] In F11 copied down :

=SUM(B11:E11)

Regards
Bosco

very elegant Bosco - could you help me understand the part where you are following the 2nd match function with +columns....*; I could not follow this part. What is it meant to do? Thanks
Never mind - I figured out what you are doing here with the formula Very simple but impressive. Thanks

 

[monoj chakraborty]

Another offering. In the vicinity of cell B13, array-entered:

=SUMPRODUCT(($B$1:$AN$1=$A13 & "_1")+($B$1:$AN$1=$A13 & "_2")+($B$1:$AN$1=$A13 & "_3"),INDEX($B$2:$AN$7,MATCH($A$12,$A$2:$A$7,0),))

and by way of a cross-check, at A22 and below a re-arrangement of the data with a pivot table.

I am digging this tooooo!!

 

[monoj chakraborty]

I am digging this tooooo!!

This is a nice solution too....Pivoting would be an obvious choice...but how did you convert the file to the format required for Pivoting? did you use any tool or the query option in microsoft? also how did you convert the column headers, which are in text, to date format?
Thanks

 

[p45cal]

but how did you convert the file to the format required for Pivoting?
<snip>
also how did you convert the column headers, which are in text, to date format?

Wrote a little macro:

Sub blah()
Application.ScreenUpdating = False
Set Destn = Range("A23")
For Each cll In Range("B2:AN7").Cells
If Len(Cells(1, cll.Column).Value) > 3 Then 'only include headers long enough to have month number (excludes full year values).
  Destn.Value = Cells(cll.Row, 1).Value
  Destn.Offset(, 1).Value = DateSerial(2000 + CInt(Mid(Cells(1, cll.Column).Value, 2, 2)), CInt(Split(Cells(1, cll.Column).Value, "_")(1)), 1)
  Destn.Offset(, 2).Value = cll.Value
  Set Destn = Destn.Offset(1)
  End If
Next cll
Application.ScreenUpdating = True
End Sub