Rolling Total for 2 adjacent columns

[Quillcon]

This is my first post so please be patient with me! I use Excel 2007.

I have a data set that tracks ingredients in chemical products. For example sake the chemical product is a mixed color, and the calculated ingredient is a primary color.

I need to calculate the quantity of two primary colors based on their percentage of the mixture.

Column A contains the mixed color names. Columns B and C contain the percentage of the primary in each mix.

I began tracking this data in January 2009. Each month I add 3 columns to the worksheet. The 1st column contains the monthly mixed color quantity used. The 2nd and 3rd columns contain the calculation of the two primary colors.

I need the formula to calculate the 12 month rolling total for each primary color. (For July 2014 I need to calculate July 2013 through June 2014)

A

B

C

E

F

G

H

I

J

K

L

M

N

O

P

1

1/1/14

2/1/14

3/1/14

2

Color

% Red

% Blue

Qty

%Red

%Blue

Qty

%Red

%Blue

Qty

%Red

%Blue

rolling %Red

rolling %Blue

3

Orange

.5

3

=E2*$B$2

=E2*$C$2

6

=H2*$B$2

=H2*$C$2

2

=K2*$B$2

=K2*$C$2

4

Purple

.5

.5

7

=E3*$B$3

=E3*$C$3

2

=H3*$B$3

=H3*$C$3

3

=K3*$B$3

=K3*$C$3

5

Green

.5

4

=E4*$B$4

=E4*$C$4

4

=H4*$B$4

=H4*$C$4

14

=K4*$B$4

=K4*$C$4

<tbody>
</tbody>

 

[npvrader]

Use sumif function. Always keep blank column between summary and data (in this case column n, but will change as months are added). Change the offset range in formula below from -9 to -36 (assuming each period has 3 columns ....3x12=36).

=SUMIF(OFFSET(N$2,0,0,1,-9),O$2,OFFSET(N3,0,0,1,-9)) - this goes in O3 (or first row with live data)
=SUMIF(OFFSET(N$2,0,0,1,-9),P$2,OFFSET(N3,0,0,1,-9)) - this goes in p3

Without going into too deeply on sumifs and offsets:
The sumif is looks for a range of values (the first offset) and compares it to the criteria o$2 and if true adds anything that is found in the range defined by the second offset. The offsets create a range reference starting from the anchor N$2, 1 row x 9 column (to the left ) range.

Let me say that structuring data like this makes your reporting inflexible. While this was expedient in the beginning, it is short sighted. A better structure would have been to have a row for each month with columns a-g and add a column for date. Then you could have a structure that you could report via a pivot table and easily incorporate rolling 12's ,quarterly, yearly, etc..... Again, I realize that at initial design this works, but now that the report has lasted 60 months with no end in sight a structure becomes very important.

By the way, thanks for providing the detailed and well written explanation of what you wanted. I see post after post that lack definition or are so poorly written that you can't help. Good job!

 

[Quillcon]

Thank you!
I inherited the tracking table from a previous consultant and am realizing that the % doesn't necessarily need to be calculated each month. Thank you for identifying what I had in my head but had not been able to verbalize.

Now I have a question about pivot tables though. Should I ask that in a new thread and link back to this?

 

[npvrader]

Probably best to start another thread unless the question is specifically how to convert this data to a pivot table.