How to best use sumif and if function

[LEGINXX]

10 10 xxx
12 10
12 10
10 10
10
10
10
10
10
10
10
10
10
10
10
In the above table I want to return a value. (xxx)Column A1 (value of 10) is used to count 10 occurances of column b. When i get to a2 i want it to add 12 occurances of column b. So in the first example it retrns a value of 100 and in the case of a2(12) a value of 120 in c2.I could do this with using the if function but is there a better way?

[zbor]

Write in C1 =A1*B1

[LEGINXX]

hi not quite what i want ..my fault as the columns seem to have moved when i copied them across.



a b c
1 10 10 xxx(100)
2 12 10 yyy(120)
10
10
10
10
10
10
10
10
10
10

I am looking to add the values of column b by the value in a1. the numbers in b are illustrative and will be different. i will want to change a1 to maybe 12 and without changing the formula calculte c1 based on adding up 12 occurences of co, b. If it helps its to work out inventory values based on forward weeks sales , where colb =sales cola is the weeks of forward stock to cover those sales and colc is the value of that stock based on the weeks weeks cover and sales. I could do it using "if" function but its a real pain.

[NBVC]

Do you mean?

=SUM(B1:INDEX(B:B,A1)

[LEGINXX]

Thanks but not delivering the right answer. The data seems to have moved over a column for some reason when posted.Just to confirm how it should look a1 is variable in the example its 10 but could be anything. (and i would be entering a variable in a2,a3 etc to perform the same calculation. Column b is a column of 10's and c1 is the returned value. So working it through if i enter a 10 in a1 c1 should =100 and if i put a 12 in it should be 120.

Hope that is clearer. Index did not solve this

[arthurbr]

Why not post a sample sheet? Probably would have avoided unnecessary loss of time

[LEGINXX]

How do I do that?

[arthurbr]

Edit your OP - Click Go Advanced - Click the paperclip and follow the wizard ( for more info see the FAQ)

[LEGINXX]

Hi here you go attached file as requested

[arthurbr]

Sorry, I meant a spreadsheet, not a picture

[LEGINXX]

here you go

[NBVC]

So this formula doesn't work?

=SUM(C6:INDEX(C:C,B6+5))

[LEGINXX]

no it does not work. It works for D6 but needs to work down the column and thats my issue. So as an example in week 2, 10 weeks stock based on 10 weeks of sales at 10k should be 100, your formula gives 90.(d7) Then 80,70

Thanks for trying

[NBVC]

Ok, how about?

=SUM(C6:INDEX(C:C,ROW()+B6-1))

[LEGINXX]

brilliant thanks so much. Not sure why it works but it does

[arthurbr]

brilliant thanks so much. Not sure why it works but it does

If you use the Evaluate Formula functionality, you will see how it works