formula that changes every 12 rows

[ursvasan]

I am a basic excel user faced with a complex excel problem and have no idea how to do it now. I have 3 columns (category, month, sales) and i would like to compute the 4th column (weight) as a function of 3rd column. basically i want to compute the ratio of sale for each month over the entire year for each category. I tried a few formulas using ROW, MOD and IF, but this is literally my first time doing such complicated stuff in excel, so i am blank. Any help would be highly appreciated.

A B C D
category month sales weight
0 1 10769 =c1/sum(c$1:c$12)
0 2 10549 =c2/sum(c$1:c$12)
0 3 11014
0 4 10219
0 5 13945
0 6 12859
0 7 12187
0 8 9881
0 9 8017
0 10 11466
0 11 27178
0 12 61449
1 1 4411 =c13/sum(c$13:c$24)
1 2 3962 =c14/sum(c$13:c$24)
1 3 3379
1 4 5940
1 5 7250
1 6 6239
1 7 5662
1 8 4460
1 9 3745
1 10 4645
1 11 9723
1 12 22811
2 1 3649 =c25/sum(c$25:c$36)
2 2 4050 =c25/sum(c$25:c$36)
2 3 4675
2 4 5309
2 5 7431
2 6 10529
2 7 9242
2 8 7989
2 9 6246
2 10 6976
2 11 10834
2 12 19560 =c36/sum(c$25:c$36)



Thanks
Srini

 

[Aladin Akyurek]

Would you replace =c1/sum(c$1:c$12) of your exhibit with real values you want to see?

 

[Zakkaroo]

Do your categories repeat? Because it seems (from your example data) as if you're trying to sum by very specifically selecting the range, but in your example, you could just do:

=C1/sumif(A:A,A1,C:C)

 

[ursvasan]

I have the sales data for each category for 12 months sorted by category, month. And categories are not in range 0 to n. i mean after 0,1, 2 it can become 5,10,20. But they are in sorted order. each category appears only 12 times and consecutively as its sorted.

And yes, I want column D to have its values (not the formula). I showed the formula i want to use to describe the question.

 

[Aladin Akyurek]

...

And yes, I want column D to have its values (not the formula). I showed the formula i want to use to describe the question.

What values? That's what you are asked if they are not say 0.2, 0.897, etc?