Cell reference formula problem

[Rocketeer]

I have problems finding the correct formula for my Excel problem. My spread
sheet is used for statistics for my web shop.

In column A I have values representing sales figures for each order.
In column B I have a figure representing number of orders during a day.
These calculations work. Note that column B consists mostly of blanks.

In column C I want to calculate the turnover for all orders that day.

Example:

Col A Col B Col C
Row 3 150 4 1881
Row 4 143
Row 5 593
Row 6 995
Row 7 498 3 1056
Row 8 127
Row 9 431

For row 3 I tried with the following formula in cell C3:

=IF(ISBLANK(B3);"";SUM(A3:INDIRECT("A"&B3)))

In other words, if B3 is blank do nothing. If it contains a value then
SUM(A3:A6). But it didn't work properly. The sum function is not correct and
I get #Ref! error in column C for all rows containing blanks in column B.

I can see why because if I stand in C3 I want to sum A3:A3+3 or A3:A value
in B3 - 1. The #Ref! when standing in C4 is due to the blanks in column B,
which will give the sum function SUM(A4:INDIRECT("A").

Note that the values in columns A, B and C will be variable depending on the
attributes of the orders in my web shop.

Any help is appreciated.

Best

/Peter

 

[T. Valko]

Try this in C3 and copy down as needed:

=IF(B3="","",SUM(OFFSET(A3,,,B3)))

 

[Teethless mama]

In C3: =IF(B3="","",SUM(A3:INDIRECT("A"&ROW()-1+B3)))

copy down

or

In C3: =IF(B3="","",SUM(A3:INDEX(A3:$A$99,B3)))

copy down

 

[T. Valko]

=IF(B3="","",SUM(OFFSET(A3,,,B3)))

You might have to change the comma separators to semi-colons:

=IF(B3="";"";SUM(OFFSET(A3;;;B3)))

 

[Rocketeer]

Thanks guys! All your examples worked.

 

[T. Valko]

You're welcome. Thanks for the feedback!